TRY_CONVERT function in SQL Server 2012


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
TryCon1

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

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

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

And the output is
TryCon3The 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')

TryCon4CONVERT will give an error message stating the conversion failed.

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

TryCon5TRY_CONVERT will give a NULL.

 

Advertisements

Tagged: , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: