SQL Server CAST() Function: Convert DataType

SQL Server CAST() function converts an expression of one data type to another data type.

It is recommended to use the TRY_CAST() function over the CAST() function to minimize errors.

CAST(expression AS final_data_type(length))

Parameters

expression: Any valid expression that has to be converted.

final_data_type: The target data type to which you want to convert the given expression. This is an optional parameter.

length: An optional integer that specifies the length of the target data type.

Return Value

Returns the given expression converted to the target data type. It returns an error when converting a non-numeric char, nchar, varchar, or nvarchar value to decimal, float, int, or numeric.

It returns an error when converting an empty string to int or numeric data type.

When you convert data types with different decimal places, then the CAST() function either truncates the resulting value or rounds it off to the higher or lower value.

From	To	Behavior
numeric	Numeric	Round
numeric	Int	Truncate
numeric	Money	Round
money	Int	Round
money	numeric	Round
float	int	Truncate
float	numeric	Round
float	datetime	Round
datetime	int	Round

Example 1:

In the following example, a decimal is converted to an integer data type.

Example: CAST()

SELECT CAST(22.24 AS int) AS Result;

Example 2:

When you convert data types with different decimal places, then the CAST() function either truncates the resulting value or rounds it off to the higher or lower value.

Example: CAST()

SELECT CAST(22.24 AS dec(3,1)) AS Result1, 
    CAST(22.24 AS dec(3,0)) AS Result2;

Example 3:

In the following example, CAST() function is used to convert a string expression to a datetime value.

Example: CAST()

SELECT CAST('11/23/2022' AS datetime) as result;

Example 4:

In the following example, the CAST() is used to concatenate non-character value to a string expression. It is used with the Salary column which is of integer type and converts it to string value.

Example: CAST()

SELECT 'The Salary of ' + FirstName + ' is $' + CAST(Salary AS varchar(10)) FROM Employee;

Example 5:

In the following example, the CAST() function gives an error while converting a string to an int.

Example: CAST()

SELECT CAST( 'abcd' AS int) AS Result;

Example 6:

In the following example, the CAST() function converts a character string to a character of length 5. It truncates string to 5 characters.

Example: CAST()

SELECT CAST('Hello World' AS char(5)) AS Result;

Example 7:

Here, the CAST() is used with the HireDate column to convert from date to datetime.

Example: CAST()

SELECT HireDate, CAST(Hiredate AS datetime) AS Result FROM Employee;