The SQL CAST function converts an input expression of one data type into another data type.
Data types of values often convert to meet different format requirements in SQL queries. Sometimes these are implicit conversions. For example, while multiplying a decimal value with an integer value, the SQL engine first converts the integer value into a decimal data type and then performs the calculation. Sometimes these are explicit conversions, where the user changes the data type of a value using functions like SQL CAST.
SQL CAST Usage Notes
- The data type argument is the target data type of the input expression. Therefore, It cannot be an alias data type.
- The length argument is optional. It specifies the length of the return value and it has a default value of 30.
- The function returns an expression with the specified data type.
CAST ( input_expression AS data_type [( length )])
SQL CAST Examples
We will use the AdventureWorks2019 database for all the examples.
First, let’s look at a simple example of using a SQL CAST function on an integer value to change its data type to a decimal.
In the following example, the SQL CAST function converts a DATETIME value into NVARCHAR.
The above query converts the DATETIME data type of the TransanctionDate column to an nvarchar value.
In the next example, we will apply an arithmetic operation on two columns with different data types and cast the result into one of these data types.
The ListPrice column has the data type of money, whereas quantity has the data type of int. After applying multiplication operation and aggregate function, we use CAST that returns the result in int data type. The Group By clause groups the output rows by their shopping cart Ids.
SQL CAST function also helps to join expressions, a process called concatenation.
The above query converts the data type of ExpMonth column of CreditCard table to varchar using CAST. In this way, the EXPMonth column with the original datatype of tinyint concatenates with the rest of the literal expression.