SQL Server TRY_CONVERT() Function

TRY_CONVERT() attempts to convert an expression to a target data type. It returns the converted value when the conversion succeeds and NULL when a supported conversion fails. It is available in SQL Server 2012 (11.x) and later. See Microsoft’s TRY_CONVERT() reference.

Syntax

TRY_CONVERT(data_type [ ( length ) ], expression [ , style ])

• data_type: the target SQL Server type.
• length: an optional length for target types that accept one.
• expression: the value to convert.
• style: an optional style value that uses the same rules as the style argument to CONVERT().

Return NULL for invalid values

For a supported conversion, TRY_CONVERT() returns NULL instead of raising the conversion error that CAST() or CONVERT() would raise for an invalid value:

SELECT TRY_CONVERT(int, '42') AS valid_value,
       TRY_CONVERT(int, '42x') AS invalid_value;

This can help inspect imported text before converting it to a numeric column. Exclude source NULL values when you need to identify only invalid, non-NULL input:

SELECT ImportID, RawValue
FROM dbo.ImportRows
WHERE RawValue IS NOT NULL
  AND TRY_CONVERT(int, RawValue) IS NULL;

Do not silently replace these values with zero or discard them without an application rule. For a common conversion failure and its remediation, see SQL Server Error 245.

Use a style for date strings

The optional style parameter lets you specify the expected date format. Style 103 parses a dd/mm/yyyy date:

SELECT TRY_CONVERT(date, '31/12/2024', 103) AS parsed_date;

Result:

Use an explicit style when parsing known non-ISO input formats. Prefer unambiguous ISO date strings when you control the input format.

An explicitly disallowed conversion still raises an error

TRY_CONVERT() handles failures for conversions SQL Server permits, but it does not make a disallowed conversion possible. For example, converting an int directly to xml still raises an error:

SELECT TRY_CONVERT(xml, 4);

SQL Server reports that an explicit conversion from int to xml is not allowed. Use CONVERT() when a conversion is known to be valid and should raise an error if it is not.

TRY_CAST or TRY_CONVERT

Use TRY_CAST(expression AS data_type) for a conversion that does not need a style code. Choose TRY_CONVERT(data_type, expression, style) when date, time, numeric, or other CONVERT() style behavior is required. Both functions return NULL when a supported conversion fails, and both still raise an error for explicitly disallowed conversions. See Microsoft’s TRY_CAST() reference.