Microsoft SQL Server TRY CONVERT


SQL Server 2012

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 - It is an optional parameter which determines formatting. Suppose you want date format like “May, 18 2013” then you need pass style as 111.

DECLARE @sampletext AS VARCHAR(10);  
SET @sampletext = '123456';  
DECLARE @ realDate AS VARCHAR(10);  
SET @realDate = '13/09/2015’;  
SELECT TRY_CONVERT(INT, @sampletext); -- 123456  
SELECT TRY_CONVERT(DATETIME, @realDate, 111); -- Sep, 13 2015