How to convert value from one data type to another in SQL Server Transact-SQL? How to use the CONVERT function or the CAST function?

In Transact-SQL, you can use CONVERT statements to convert values from one data type to another, in the syntax of:

CONVERT(data_type, value[, style])

In this syntax, "data_type" specifies the data type of the output value, "value" specifies the input value, and "style" specifies the conversion style. If "style" is not provided, the default style will be used.

The CAST function does the same job as the CONVERT function using the default style using a different syntax:

CAST(value AS data_type)

CONVERT and CAST functions are considered as explicit data type conversion, which gives you more power than the implicit conversion in two aspects:

• Explicit conversion supports more cases than implicit conversion. For example, implicit conversion from DATETIME to NUMERIC is not supported. But explicit conversion from DATETIME to NUMERIC is supported.
• CONVERT function can specify a conversion style different than the default style. For example, the default style of explicit conversion from DATETIME to CHAR gives "mon dd yyyy hh:miAM (or PM)" format. But you can use CONVERT(CHAR, date-time, 126) to convert DATETIME to CHAR giving "yyyy-mm-ddThh:mi:ss.mmm" ISO format.

Here are some examples on how to convert values to different data types:

DECLARE @dt DATETIME, @rn NUMERIC(9,2);

-- Implicit conversion from NUMERIC to DATETIME
SET @dt = 0.25;

-- Implicit conversion from DATETIME to CHAR
PRINT @dt;
-------------------
Jan  1 1900  6:00AM
           
-- Explicit conversion from DATETIME to CHAR with default style
PRINT CAST(@dt AS CHAR);
-------------------
Jan  1 1900  6:00AM
           
-- Explicit conversion from DATETIME to CHAR with ISO style
PRINT CONVERT(CHAR, @dt, 126);
-------------------
1900-01-01T06:00:00           

-- Explicit conversion from DATETIME to NUMERIC
SET @rn = CONVERT(NUMERIC(9,2), @dt);
PRINT @rn;
----           
0.25

-- Implicit conversion from DATETIME to NUMERIC, not supported
SET @rn = @dt;
PRINT @rn;
-----------------------------------
Msg 257, Level 16, State 3, Line 21
Implicit conversion from data type datetime to numeric is not allowed. 
Use the CONVERT function to run this query.

⇒ Numeric Expressions and Functions in SQL Server Transact-SQL

⇐ PRINT Statements in SQL Server Transact-SQL

⇑ Variables and Data Types in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials