SQL Tutorial SQL Advanced SQL Database SQL References

SQL Server CAST() Function



The SQL Server (Transact-SQL) CAST() function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

Note: Use the TRY_CAST() function to return a NULL (instead of an error) if the conversion fails.

Syntax

CAST(expression AS data_type [ ( length ) ])

Parameters

expression Required. Specify the value to convert to another datatype.
data_type Required. Specify the datatype to convert to. See the data types of SQL Server. Note that the Alias data types cannot be used.
length Optional. Specify the length of the resulting data type, for data types that allow a user specified length. Default is 30.

Return Value

Returns the expression converted to another data_type.

Convert to INT

The CAST() function can be used to convert a value to a INT type. For example - in the example below, 123.456 is converted to INT datatype.

SELECT CAST(123.456 AS INT);
Result: 123

Convert to DECIMAL

The CAST() function can be used to convert a value to a DECIMAL type. For example - in the example below, '123.456' is converted to DECIMAL datatype.

SELECT CAST('123.456' AS DECIMAL(5, 2));
Result: 123.46

Convert to CHAR

The CAST() function can be used to convert a value to a CHAR type. For example - in the example below, 123 is converted to CHAR datatype.

SELECT CAST(123 AS CHAR(4));
Result: '123'

Convert to NCHAR

The CAST() function can be used to convert a value to a NCHAR type. For example - in the example below, 123 is converted to NCHAR datatype.

SELECT CAST(123 AS NCHAR(10));
Result: '123'

Convert to VARCHAR

The CAST() function can be used to convert a value to a VARCHAR type. For example - in the example below, 123 is converted to VARCHAR datatype.

SELECT CAST(123 AS VARCHAR);
Result: '123'

Convert to DATE

The CAST() function can be used to convert a value to a DATE type. For example - in the example below, '2018-08-18' is converted to DATE datatype.

SELECT CAST('2018-08-18' AS DATE);
Result: '2018-08-31'

Convert to TIME

The CAST() function can be used to convert a value to a TIME type. For example - in the example below, '10:38:42' is converted to TIME datatype.

SELECT CAST('10:38:42' AS TIME);
Result: '10:38:42'

Convert to DATETIME

The CAST() function can be used to convert a value to a DATETIME type. For example - in the example below, '2018-08-18 10:38:42' is converted to DATETIME datatype.

SELECT CAST('2018-08-18 10:38:42' AS DATETIME);
Result: '2018-08-18 10:38:42'

❮ SQL Server Functions