T-SQL TRY_CONVERT()

I 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:

SELECT CONVERT(INT, 'aaa')

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:

SELECT TRY_CONVERT(INT, 'aaa')

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()

SELECT ISNULL(TRY_CONVERT(INT, 'aaa'), 0)
-- OR
SELECT COALESCE(TRY_CONVERT(INT, 'aaa'), 0)

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