How to change the data type in databases

In SQL, the datatypes can be converted to some other type in two ways. There are implicit and explicit datatype conversions in databases.

Implicit conversion

These conversions are invisible to the user. The SQL server automatically converts the data from one type to another in this conversion. For example, if we want to compare int and smallint, the smallint is automatically converted to int before performing the comparison.

Explicit conversion

The CAST and CONVERT functions are used for explicit data type conversion. The CAST and CONVERT convert any value, column, variable, or expression to some other type.

Syntax

Syntax of CAST function is :

CAST ( Expression AS datatype [ ( length_of_value ) ] )

Syntax of CONVERT function is:

CONVERT ( data_type [(length_of_data )] ,New_expression[,style])

The CONVERT function allows us to input parameters in the function if we want to change the style of the output. We can not use parameters in the CAST function.

Example

Let's see some examples of data type conversion using the CAST and CONVERT functions.

CREATE DATABASE Educative
GO
USE Educative
GO
CREATE SCHEMA Dept
GO
CREATE TABLE Dept.Department
(
    Department_Id  INT PRIMARY KEY,
    Department_Name VARCHAR(30),
    Department_Floor DECIMAL(6,2)
)
GO
INSERT INTO Dept.Department 
VALUES
     (01,'Human Resource',2.00),
     (02, 'Development',3.00),
     (03, 'Technical Content',1.00)
GO 
-- CAST --
SELECT 
   Department_Id,
   Department_name,
  CAST(Department_Floor as INT)
FROM Dept.Department
GO
-- CONVERT --
DECLARE @Value AS FLOAT
SET @Value = 2234.56
SELECT CONVERT(numeric, @Value /*,0*/) as new_ConvertedVal
GO

Explanation

  • Lines 1–22: We create a database Educative , a schema Dept, and a table Department. We insert values in the tables.

  • Lines 24–30: We use the CAST function in the SELECT statement to cast the Department_Floor column from DECIMAL to INT.

  • Lines 31–36: We declare a variable of FLOAT type and assign a value to it, then we use the CONVERT function in the SELECT statement to change its type from FLOAT to NUMERIC. We also pass a parameter to style the output and get a rounded value.

Note: We cannot convert all datatypes from one type to another. For example the nchar type can only be explicitly converted to either binary type or nvarchar; it cannot be converted to image. Also implicit conversion is not supported.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved