In SQL Server, an often used function is CAST(), which converts an expression of one data type to another. But if the cast doesn’t succeed, then it returns an error.

Enter TRY_CAST().

The TRY_CAST() function doesn’t return an error if the cast fails. Instead, it returns NULL.

There are some occasions however, where it will return an error.

Syntax

The syntax goes like this:

TRY_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 1 – Cast Succeeds

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

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

Result:

7.00

In this case, the cast succeeded.

Example 2 – Cast Fails and Returns NULL

Here’s an example of the cast failing and NULL being returned:

SELECT TRY_CAST('Double Oh Seven!' AS DECIMAL(5,2));

Result:

NULL

The cast failed, and so NULL was returned.

As a comparison, here’s what happens when we use CAST() instead of TRY_CAST():

SELECT CAST('Double Oh Seven!' AS DECIMAL(5,2));

Result:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Example 3 – Cast Fails and Returns an Error

There are some occasions where TRY_CAST() will return an error.

If a cast is explicitly not permitted, then it returns an error:

SELECT TRY_CAST(10 AS xml);

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

More Information

See How CAST() Works in SQL Server for more conversion examples, and CAST() vs TRY_CAST() in SQL Server for a comparison between CAST() and TRY_CAST().

See Microsoft’s documentation for CAST() and CONVERT() for more detailed information (most of it also applies to TRY_CAST()).