Cast() function in SQL


The cast function is used to explicitly convert an expression of one data type to another.

The argument to the cast() function is an expression that includes the source value and the target data type separated by the as keyword.

1、 Grammar:

  CAST (expression AS data_type)

Parameter Description:
  expression: any valid SQL Server expression.
  AS: used to separate two parameters. Before as is the data to be processed, and after as is the data type to be converted.
  data_type: data types provided by the target system, including bigint and SQL_ Variant, cannot use user-defined data types.


When cast function is used for data type conversion, it can be accepted under the following conditions:
(1) the data types of the two expressions are exactly the same.
(2) the two expressions can be transformed implicitly.
(3) the data type must be explicitly converted.


     There is a limit to the types that can be converted. This type can be one of the following values:

  • Binary, with the effect of binary prefix: binary
  • Character type with parameter char ()
  • Date: Date
  • Time: time
  • Date time type: datetime
  • Floating point number: decimal
  • Integer: Signed
  • Unsigned integer: unsigned


2、 Note:

(1) The argument to the. Cast() function is an expression that includes source values and target data types separated by the as keyword.

SELECT CAST(’12’ AS int)




(2) . the return value is an integer value of 12. What happens if you try to convert a string representing a decimal to an integer value?

SELECT CAST(‘12.5’ AS int)




be careful:Neither the cast() function nor the convert() function can perform rounding or truncation operations. Since 12.5 cannot be represented by the int data type, a call to this function will result in an error


(3) To return a legal value, you must use a data type that can handle the value.

For this example, there are multiple data types available. If the value is converted to a decimal type by cast() function, the precision and decimal number of the decimal value need to be defined first. In this case, the precision and scale are 9 and 2, respectively. Precision is the total number of digits, including the sum of the left and right digits of the decimal point. The number of decimal places is the number to the right of the decimal point. This means that the maximum integer value that this example can support is 999999, and the minimum decimal value is 0.01.

SELECT CAST(‘12.5’ AS decimal(9,2))




(4) The default values for precision and decimal places are 18 and 0, respectively. If these two values are not provided in the decimal type, SQL server will truncate the decimal part of the number without error.

SELECT CAST(‘12.5’ AS decimal)


Writing is not easy, reprint please provide the source, thank you!