SQL Server CAST() Function

In SQL Server, manipulating data is a fundamental aspect of database management. Often, you’ll find yourself needing to transform data from one type to another, either for calculations, comparisons, or presentation purposes. This is where the CAST() function comes. In this article, we will learn about the CAST() function in SQL Server, which is used to convert a value from one data type to another.

What is the CAST() Function?

The SQL server CAST() function allows you to explicitly convert data from one data type to another. Whether you need to change a string to a number, adjust the precision of a decimal, or alter the format of a date, the CAST() function provides the flexibility to manipulate your data to meet specific requirements. Understanding how to use this function effectively can streamline your data management processes and enhance the accuracy of your database queries. The syntax of the CAST() function is as follows:

CAST ( expression AS data_type [ ( length ) ] )

Here:

• expression represents the value to be converted.
• data_type denotes the target data type to which the expression will be converted.
• length (optional) specifies the length of the target data type, particularly relevant for character data types like VARCHAR. The default value is 30.

The CAST() function can convert values of any data type to one of the following data types: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.

Examples of Using the CAST() Function

Let’s see some examples of how to use the CAST() function in SQL Server.

Example 1: Convert a String Value to an Integer

Suppose we have a decimal value ‘123’ and we want to convert it to an integer. We can use the CAST() function as follows:

SELECT CAST('123' AS INT) AS IntegerValue;

Output:

Convert String to Integer using Cast() function

Explaination: In this Example, The provided SQL query uses the CAST function to convert the string ‘123’ to an integer. The result, named IntegerValue, is the integer representation of the given string, which is 123.

Example 2: Convert a String Value to a Date

Suppose we have a date value 2024-02-08′ and we want to convert it to a varchar. We can use the CAST() function as follows:

SELECT CAST('2024-02-08' AS DATE) AS ConvertedDate;

Output:

Convert String to Date using Cast() function

Explaination: In this Example a string representing a date(‘2024-02-08’) is cast to the DATE data type.

Example 3: Convert a Integer Value to an Bit Value

Suppose we have a Integer value 1 and we want to convert it to a Bit. We can use the CAST() function as follows:

SELECT CONCAT('The bit value is: ', CAST(1 AS bit)) AS BitValue;

Output:

Convert Integer to Bit using Cast() function

Explaination:

In the provided example, we’re using the CONCAT() function along with the CAST() function to create a string that includes both text and a converted value. CAST() function convert the integer value 1 to a bit data type

Conclusion

The CAST() function in SQL Server is a versatile tool for data transformation tasks. By understanding its usage and syntax, you can seamlessly convert data between different types to suit your specific needs. Its ability to transform data types with precision and control enhances the flexibility and efficiency of database operations. Whether it’s converting strings to numbers, adjusting decimal precision, or handling other data type conversions, CAST() empowers SQL developers to tackle diverse data challenges with ease.