SQL Server CAST() Function

The CAST() function is a type conversion function in SQL Server that converts a value of one data type to another. It can convert data types such as character, date, time, and numeric types, and is commonly used to convert a string value to a numeric value.

Syntax

The syntax of the CAST() function is as follows:

CAST ( expression AS data_type [ ( length ) ] )

Here, expression is the expression to be converted, which can be a column name, a variable, a string constant, etc. data_type is the target data type to be converted to, which can be nvarchar, int, datetime, float, etc. The length parameter is optional and is used to specify the length of the target data type.

Usage

The CAST() function is commonly used in the following scenarios:

Data type conversion: Convert a value of one data type to another, such as converting a string to a numeric value or converting a datetime value to a character type.
Data type consistency: When performing data comparison, sorting, calculation, etc., the data types involved in the operation must be consistent, and the CAST() function can be used to convert the data type.
Data type truncation: When converting a larger data type to a smaller data type, data truncation may occur. In this case, the CAST() function can be used to convert the data type and ensure the accuracy of the data.

Examples

Here are two examples of using the CAST() function:

Example 1

Convert a character numeric value to an integer:

SELECT CAST('123' AS INT) AS result;

The result is:

result
123

Example 2

Convert a datetime value to a character type:

SELECT CAST(GETDATE() AS NVARCHAR(20)) AS result;