In SQL, the datatypes can be converted to some other type in two ways. There are implicit and explicit datatype conversions in databases.
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.
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 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.
Let's see some examples of data type conversion using the CAST
and CONVERT
functions.
CREATE DATABASE EducativeGOUSE EducativeGOCREATE SCHEMA DeptGOCREATE TABLE Dept.Department(Department_Id INT PRIMARY KEY,Department_Name VARCHAR(30),Department_Floor DECIMAL(6,2))GOINSERT INTO Dept.DepartmentVALUES(01,'Human Resource',2.00),(02, 'Development',3.00),(03, 'Technical Content',1.00)GO-- CAST --SELECTDepartment_Id,Department_name,CAST(Department_Floor as INT)FROM Dept.DepartmentGO-- CONVERT --DECLARE @Value AS FLOATSET @Value = 2234.56SELECT CONVERT(numeric, @Value /*,0*/) as new_ConvertedValGO
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 eitherbinary
type ornvarchar
; it cannot be converted toimage
. Also implicit conversion is not supported.
Free Resources