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.


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.


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.


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:


Example 2

Convert a datetime value to a character type:


The result is:

Mar 11 2023 12:00AM


The CAST() function is one of the commonly used type conversion functions in SQL Server, which can convert values of different data types to help us process and calculate data. When using the CAST() function, it is important to pay attention to the range and precision of the target data type, as well as the possibility of data truncation.