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()
).