When converting between data types in SQL Server, you will run into various functions that seemingly do the same thing. However, there are usually differences between these functions that might not be apparent at first glance. An example of this is the difference between the CAST() and TRY_CAST() functions.

This article demonstrates the difference between these functions when using SQL Server.

The main difference between the CAST() and TRY_CAST() functions is in the way they handle data that can’t be converted.

The CAST() Function

The CAST() function converts an expression of one data type to another. This allows you to do things like, concatenate two values of different data types. Like this:

SELECT 'Comments: ' + CAST(9 AS varchar(12)) AS Result;

Result:

Result     
-----------
Comments: 9

This works fine when the data can be converted. But what if the data can’t be converted?

In that case you’ll get something like this:

SELECT 'Comments: ' + CAST(10.00 AS varchar(1)) AS Result;

Result:

Error: Arithmetic overflow error converting numeric to data type varchar.

This is fair enough – if SQL Server can’t convert the data, you need to know!

However, there is another way to do it.

The TRY_CAST() Function

The TRY_CAST() function works just like CAST() except that if the data can’t be converted, it returns null (instead of throwing an error like CAST() does):

SELECT 'Comments: ' + TRY_CAST(10.00 AS varchar(1)) AS Result;

Result:

Result
------
null

This can be handy if you want to use conditional programming so that the application performs a different task depending on whether the data can be converted or not.

Example:

SELECT   
    CASE WHEN TRY_CAST(10.00 AS varchar(2)) IS NULL   
    THEN 'Cast failed'  
    ELSE 'Cast succeeded'  
END AS Result;

Result:

Result     
-----------
Cast failed

Invalid Data Types

The TRY_CAST() function only works this way when using valid data types. So you’ll get an error if you explicitly supply a data type that’s not permitted.

Example:

SELECT 'Comments: ' + TRY_CAST(10.00 AS Miami) AS Result;

Result:

Error: Type Miami is not a defined system type.