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