SQL Server Try_Convert Function

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

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

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

Data_type - The datatype into which to convert. Here length is an optional parameter which helps to get result in specified length.

Expression - The value to be convert

Style - style accepts the same values as the style parameter of the CONVERT function.

[ For more information, see CAST and CONVERT (Transact-SQL). ]

DECLARE @sampletext AS VARCHAR(10);
SET @sampletext = '123456';

DECLARE @realDate AS VARCHAR(10);
SET @realDate = '12/09/2015';

SELECT TRY_CONVERT(INT, @sampletext); -- 123456
SELECT TRY_CONVERT(DATETIME, @sampletext); -- NULL
SELECT TRY_CONVERT(DATETIME2, @realDate); -- 2015-12-09 00:00:00.0000000

Here is more example

SET DATEFORMAT dmy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --NULL
GO
SET DATEFORMAT mdy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --2010-12-31 00:00:00.0000000
GO