TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

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

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TRY_CONVERT will give a NULL.

Tagged: Analytical functions in SQL Server 2012, CONVERT, Functions, Microsoft SQL Server, New features in SQL Server 2012, New Functions in SQL Server 2012, Programming, SQL Server 2012, Transact-SQL, TRY_CONVERT