The SQL CAST function is used to convert an expression of one data type to another. This conversion can be necessary when you need to perform operations on data of different types or when you want to display data in a specific format. The syntax for the CAST function is as follows:
CAST (expression AS data_type [ (length) ])
Here, expression is the value you want to convert, and data_type is the target data type to which you want to convert the expression. The optional length parameter is used for types that require a length, such as VARCHAR or CHAR.
Examples
Let’s look at some examples to illustrate the use of the CAST function:
Basic Usage
SELECT CAST('123' AS INT) AS ConvertedValue;
In this example, the string ‘123’ is cast to an integer, resulting in the value 123.
Date Conversion
SELECT CAST('2023-11-29' AS DATE) AS ConvertedDate;
Here, a string representing a date is cast to the DATE data type.
Decimal to Integer
SELECT CAST(15.75 AS INT) AS ConvertedInteger;
The decimal value 15.75 is cast to an integer, and the fractional part is truncated.
Using Length Parameter
SELECT CAST('Hello' AS VARCHAR(10)) AS ConvertedString;
In this case, the string ‘Hello’ is cast to a variable character string with a maximum length of 10 characters.
It’s important to note that not all conversions are possible, and attempting to cast incompatible types may result in an error. For example, casting a non-numeric string to an integer would result in an error. In such cases, you may need to use other functions like CONVERT or handle the conversion in your application code.
Additionally, starting from SQL Server 2012, you can use the TRY_CAST function, which returns NULL if the cast is not possible instead of raising an error. This can be useful when dealing with data of uncertain or varied types.
SELECT TRY_CAST('ABC' AS INT) AS ConvertedValue;
-- Result: NULL
In summary, the CONVERT function is a powerful tool for converting data from one type to another in SQL Server, but it should be used carefully to ensure that the conversion is valid for the given data.
