SQL Server CAST Function

Summary: in this tutorial, you will learn how to use the SQL Server CAST() function to convert a value or an expression from one type to another.

Introduction to SQL Server CAST() function

Let’s see the following query:

SELECT 1 + '1' AS result;Code language: PHP (php)

It returns 2 as a number:

result
-----------
2

(1 row affected)

In this statement, SQL Server implicitly converts the character string '1' to the number 1.

When you use two values with different data types, SQL Server will try to convert the lower data type to the higher one before it can process the calculation. This is known as an implicit conversion in SQL Server.

In contrast to implicit conversions, we have explicit conversions where you call the CAST() function to explicitly convert a value of one type to another:

SELECT 1 + CAST(1 AS INT) result;Code language: PHP (php)

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

CAST ( expression AS target_type [ ( length ) ] )  Code language: CSS (css)

In this syntax:

• expression can be a literal value or a valid expression of any type that will be converted.
• target_type is the target data type to which you want to convert the expression. It includes INT, BIT, SQL_VARIANT, etc. Note that it cannot be an alias data type.
• length is an optional integer that specifies the length of the target type. The length defaults to 30.

The CAST() function returns the expression converted to the target data type.

SQL Server CAST() function examples

Let’s take some examples of using the CAST() function.

A) Using the CAST() function to convert a decimal to an integer example

This example uses the CAST() function to convert the decimal number 5.95 to an integer:

SELECT CAST(5.95 AS INT) result;Code language: CSS (css)

Here is the output:

result
-----------
5

(1 row affected)

B) Using the CAST() function to convert a decimal to another decimal with different length

The following example uses the CAST() function to convert the decimal number 5.95 to another decimal number with the zero scale:

SELECT CAST(5.95 AS DEC(3,0)) result;Code language: CSS (css)

The output is as follows:

result
-------
6

When you convert a value of the data types in different places, SQL Server will return a truncated result or a rounded value based on the following rules:

C) Using the CAST() function to convert a string to a datetime value example

This example uses the CAST() function to convert the string '2019-03-14' to a datetime:

SELECT 
    CAST('2019-03-14' AS DATETIME) result;Code language: PHP (php)

The output is:

result
-----------------------
2019-03-14 00:00:00.000

(1 row affected)Code language: CSS (css)

D) Using CAST() function with arithmetic operators

We will use the sales.orders and sales.order_items tables from the sample database for the demonstration:

The following statement uses the CAST() function to convert the monthly sales in 2017 to integer values.

SELECT 
    MONTH(order_date) month, 
    CAST(SUM(quantity * list_price * (1 - discount)) AS INT) amount
FROM sales.orders o
    INNER JOIN sales.order_items i ON o.order_id = i.order_id
WHERE 
    YEAR(order_date) = 2017
GROUP BY 
    MONTH(order_date)
ORDER BY 
    month;Code language: PHP (php)

The following picture shows the output:

In this tutorial, you have learned how to use the SQL Server CAST() function to convert a value from one type to another.