TRY_CONVERT (Transact-SQL)

data_type [ ( length ) ]

The data type into which to cast expression.

expression

The value to be cast.

style

Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT (Transact-SQL).

The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

TRY_CONVERT is a new keyword in compatibility level 110.

This function is capable of being remoted to servers that have a version of SQL Server 2012 and above. It will not be remoted to servers that have a version below SQL Server 2012.

A. TRY_CONVERT returns null

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Result
------------
Cast failed

(1 row(s) affected)

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Result
----------------------
NULL

(1 row(s) affected)

B. TRY_CONVERT fails with an error

SELECT TRY_CONVERT(xml, 4) AS Result;
GO

Explicit conversion from data type int to xml is not allowed.

C. TRY_CONVERT succeeds

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Result
----------------------------------
2010-12-31 00:00:00.0000000

(1 row(s) affected)

Reference