Pete On Software

RSS Feed

T-SQL TRY_CONVERT()

SQL Server 2012I am a little behind the curve on this one. Starting in SQL Server 2012, Microsoft introduced the TRY_CONVERT() function. This function works the same way as the CONVERT() function, but if the convert doesn’t work, you get NULL instead of an error.

Let’s take a look at the old way:

This returns the message “Conversion failed when converting the varchar value ‘aaa’ to data type int.” If you are doing a CONVERT() on some column in a large dataset, this often can throw you while you try to sort out what went wrong. Enter TRY_CONVERT(). Here is the new syntax:

This just returns NULL and doesn’t error at all. In this case, you can easily provide a default value for the conversion by using ISNULL() or COALESCE()

Pretty darn awesome and about time (well, even four years ago, it was about time!).

One Comment

T-SQL IIF  on December 20th, 2016

[…] T-SQL TRY_CONVERT() […]

Leave a Comment