SQL CAST Function (Transact SQL)

The SQL CAST function converts an input expression of one data type into another data type.

 Description

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.

Syntax

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.

/* Answer */
SELECT CAST (5 AS decimal) output;

In the following example, the SQL CAST function converts a DATETIME value into NVARCHAR.

/* Answer */
SELECT CAST (TransactionDate AS nvarchar) result
from Production.TransactionHistory

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.

/* Answer */
SELECT ShoppingCartID,
       CAST(SUM(quantity * ListPrice) AS int) Amount
FROM   sales.ShoppingCartItem s
       INNER JOIN Production.Product p ON s.ProductID = p.ProductID
GROUP BY ShoppingCartID

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.

/* Answer */
SELECT 'The expiray month of credit card ' + 
       CardNumber + ' is ' + 
       CAST(ExpMonth AS VARCHAR(12)) AS ExpiryMonth  
FROM   Sales.CreditCard

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.

See Also

• Know When to Use Cast versus Convert
• SQL Conversion Functions