CAST Function in SQL Server

In this SQL Server tutorial, you will learn about the CAST function in SQL Server, where you will understand how to cast or transform the datatype of the value to another datatype.

Finally, you will learn how to implement the CAST() function on the column of the table, also how to concatenate the CAST() function with string to present the data in a meaningful way.

Table of Contents

The CAST() function transforms the datatype of the value to another datatype. It works the same as the CONVERT() function but with limited options.

The syntax is given below.

CAST(exp_or_value AS data_type);

Where,

CAST(): It is the function that casts the data type of the value to another datatype.
exp_or_value: It is the expression or value or the table column that you want to cast from one datatype to another datatype.
AS: It is a keyword that means after this keyword you are going to specify the datatype.
datatype: Name of the datatype such as int, float varchar, etc to which the given value is being converted.

Remember when you need to perform explicit type conversion, then you use the CAST() function. For implicit conversion SQL Server handles itself.

Let’s see with different examples how to convert the datatype of the value to a different datatype.

CAST Function in SQL Server: Cast Int to String

If you have integer values such as 234,666, etc, you can these values into a string (or varchar datatype). For that use the below query.

SELECT CAST(8953 AS VARCHAR) AS IntToString;

CAST Function in SQL Server Cast Int to String

The integer value 8953 is converted into a string (varchar datatype) using the CAST() function that you see in the above picture.

Here before the AS keyword is the value that you want to convert and after the AS keyword is the datatype to which you want to convert the value 8953.

CAST Function in SQL Server: Cast String to Date and Time

So if you have a string such as ‘2023-04-12 12:30:01’ and want to change its data type from string (varchar) to date and time (datetime), you can convert that using the CAST() function.

For example, use the below query.

SELECT CAST('2023-04-12 12:30:01' AS DATETIME) AS StringToDateTime;

CAST Function in SQL Server String to Date and Time

Using the above query, the string value ‘2023-04-12 12:30:01’ is converted into the exact DATETIME datatype using the CAST() function.

CAST Function in SQL Server: Cast Decimal to Integer

The CAST() function can also cast the datatype of value from decimal to integer, but it truncates the decimal part of the value while changing the datatype to an integer.

Use the below query to cast the decimal value to an integer value.

SELECT CAST(167.33 AS INT) AS DecimalToInt;

CAST Function in SQL Server Cast Decimal to Integer

When you execute the above query it casts the value 167.33 to integer value 167 by removing the decimal part of the value that you can see in the above output.

This is how you can use the CAST() function for datatype conversion.

CAST Function in SQL Server: Casting Table Column Value

If you have a column in your table and want to transform the column value to another datatype, you can use the CAST() function for that.

Suppose you have a Sales table with columns SaleID, ProductName, SaleDate, and SalesPrice as shown below.

CAST Function in SQL Server Casting Sales Table Column Value

Now you want to show the message for each product like the “Laptop Computer sale price is 999.999” where you need to also concatenate the string with the value whose datatype you want to change.

For that use the below query.

SELECT ProductName + ' sale price is ' 
 + CAST(SalesPrice as VARCHAR) as ProductSalePrice
FROM Sales;

CAST Function in SQL Server Casting Table Column Value

In the above query, the plus (+) operator is used to concatenate the ProductName column value, string ‘sale price is ‘ with the CAST() function. As a result, it shows a meaningful message for each product such as the LED TV sale price is 799.1234.

Here you need to understand that you can concatenate the CAST() function with string value. This is how to use the CAST() function with a table column in SQL Server.

Conclusion

In this SQL Server tutorial, you covered how to use the CAST() function to cast one datatype to another datatype. Also learned to cast the datatype of the table column value from decimal to string (varchar datatype).