In SQL Server, the TRY_CONVERT()
function is very similar to the CONVERT()
function, except that TRY_CONVERT()
doesn’t return an error if the conversion fails (CONVERT()
does).
Instead, the TRY_CONVERT()
function returns NULL
if the conversion doesn’t succeed.
There are some occasions however, where TRY_CONVERT()
will return an error.
Syntax
The syntax goes like this:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
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.
The optional style
argument can be used to specify how the function should translate the expression
argument. For example, you could use this argument to specify the date format.
Example 1 – Conversion Succeeds
Here’s an example of converting a string to decimal:
SELECT TRY_CONVERT(DECIMAL(5,2), '007');
Result:
7.00
In this case, the conversion succeeded.
Example 2 – Conversion Fails and Returns NULL
Here’s an example of the conversion failing and NULL
being returned:
SELECT TRY_CONVERT(DECIMAL(5,2), 'Three');
Result:
NULL
The conversion failed, and so NULL
was returned.
As a comparison, here’s what happens when we use CONVERT()
instead of TRY_CONVERT()
:
SELECT CONVERT(DECIMAL(5,2), 'Three');
Result:
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
Example 3 – Conversion Fails and Returns an Error
There are some occasions where TRY_CONVERT()
will return an error.
If a conversion is explicitly not permitted, then it returns an error:
SELECT TRY_CONVERT(xml, 10);
Result:
Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type int to xml is not allowed.
Example 4 – The style
Argument
We can use the optional style
argument to specify how the expression should be translated.
Example:
SET LANGUAGE British;
SELECT
TRY_CONVERT(date, '09/02/2030') AS "British",
TRY_CONVERT(date, '09/02/2030', 101) AS "US",
TRY_CONVERT(date, '09/02/30', 1) AS "US (short)",
TRY_CONVERT(date, '20300902', 112) AS "ISO",
TRY_CONVERT(date, '09.02.2030', 104) AS "German";
Result:
Changed language setting to British. +------------+------------+--------------+------------+------------+ | British | US | US (short) | ISO | German | |------------+------------+--------------+------------+------------| | 2030-02-09 | 2030-09-02 | 2030-09-02 | 2030-09-02 | 2030-02-09 | +------------+------------+--------------+------------+------------+
Here, I set my language to British
, then ran TRY_CONVERT()
multiple times, each using a different style
argument (except for the first one, which uses the default language of my session – British).
We can see that the style argument affects how the expression is translated.
More Information
See CONVERT()
in SQL Server for more conversion examples, and CONVERT()
vs TRY_CONVERT()
in SQL Server for a comparison between CONVERT()
and TRY_CONVERT()
.
See Microsoft’s documentation for CAST()
and CONVERT()
for more detailed information (most of it also applies to TRY_CONVERT()
).