TRY_CONVERT

I am sure you would have used CONVERT function many times.

image

If you see the above data set, last date value is an invalid date. If you are integrating with legacy systems, you will these types of records.

They you might need to convert these into a date time value. Naturally, you will use, CONVERT function.

image

But it will fail.

image

In Denali or SQL Server 2012 (as per CTP3) you have new function called TRY_CONVERT.

image

With this new function, you won’t get the error as well as you will be able to find out the errors as well.

Following query will return all the rows where you have an invalid date value.

image

You can use this for other data types as well.

Download the code from here

Comments

Popular posts from this blog

GROUP_CONCAT or LISTAGG

An Error Occurred during decryption when creating a linked server.

Cannot show the editor for this task.