In SQL Server, the CAST() function converts an expression of one data type to another.

Here’s a quick overview of the function with examples.

Syntax

The syntax goes like this:

CAST ( expression AS data_type [ ( length ) ] )  

Where expression is the expression to convert, data_type is the new data type, and length is an optional length for the new data type.

Example

Here’s an example of casting a string as decimal:

SELECT CAST('007' AS DECIMAL(5,2));

Result:

7.00

String to Date

Here’s an example of casting a string to a date:

SELECT CAST('09 Feb 2030' AS date);

Result:

2030-02-09

In this case the CAST() function was able to determine which date parts are which because I provided the date in a format that it recognises.

Passing a value that can’t be cast as a date results in an error:

SELECT CAST('Next Xmas' AS date);

Result:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Locale

The language of the current session can make a difference in how dates are interpreted.

Here’s what happens when using the British language:

SET LANGUAGE British;
SELECT CAST('09/02/2030' AS date);

Result:

2030-02-09

In this case, the date was interpreted as being the ninth day of February.

Let’s change the language to us_English:

SET LANGUAGE us_English;
SELECT CAST('09/02/2030' AS date);

Result:

2030-09-02

This time it interpreted the date as being the second day of September.

String Concatenation

Here’s an example of casting a numeric value to a string in order to concatenate the value with a string:

SELECT 'Likes: ' + CAST(178 AS VARCHAR(10));

Result:

Likes: 178

Here’s what happens if we don’t cast it:

SELECT 'Likes: ' + 178;

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Likes: ' to data type int.

When attempting to concatenate two values with different data types, SQL Server needs to implicitly convert one of the values to use the data type of the other, so that they’re both the same data type before it can perform the concatenation. It follows the rules of data type precedence to do this conversion.

In SQL Server, the INT data type has a higher precedence than VARCHAR.

Therefore, in the above example, SQL Server attempts to convert the string to an integer. But this fails because the string cannot be converted to an integer.

Truncating Text

One handy little trick we can perform with the CAST() function is to truncate longer strings to a more readable length.

Here’s an example of truncating text selected from a database table:

SELECT
    ProductName,
    CAST(ProductName AS VARCHAR(20)) AS Truncated
FROM Products;

Result:

+---------------------------------+----------------------+
| ProductName                     | Truncated            |
|---------------------------------+----------------------|
| Left handed screwdriver         | Left handed screwdri |
| Long Weight (blue)              | Long Weight (blue)   |
| Long Weight (green)             | Long Weight (green)  |
| Sledge Hammer                   | Sledge Hammer        |
| Chainsaw                        | Chainsaw             |
| Straw Dog Box                   | Straw Dog Box        |
| Bottomless Coffee Mugs (4 Pack) | Bottomless Coffee Mu |
| Right handed screwdriver        | Right handed screwdr |
+---------------------------------+----------------------+

More Information

The CAST() function works in a similar way to the CONVERT() function, and many data conversions can be done using either one. That said, there are differences between them.

See Microsoft’s documentation for CAST() and CONVERT() for more detailed information about using this function.