SQL Server CAST() Function

The CAST() function is used to convert a value from one data type to another, helping SQL Server work with data in the required format. It is useful when we need to:

• Change strings to numbers for calculations.
• Adjust decimal precision or numeric formats.
• Convert dates and times into different formats.
• Ensure data compatibility during comparisons and operations.

The CAST() function makes data flexible, accurate, and easier to manage in SQL queries.

Syntax:

CAST ( expression AS data_type [ ( length ) ] )

• expression: The value to be converted.
• data_type: The new data type.
• length (optional): Specifies the size for data types like VARCHAR or CHAR.

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 string value '123' and we want to convert it to an integer. We can use the CAST() function as follows:

Query:

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

Output:

Example 2: Convert a String Value to a Date

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

Query:

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

Output:

Example 3: Convert an Integer Value to a Bit Value

Suppose we have an integer value 1 and we want to convert it to a BIT. CAST() converts the value, while CONCAT() is used here only to format the output string.

Query:

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

Output: