SQL: Odd TRY_CAST and TRY_CONVERT Behavior

Here’s a quick T-SQL test for you.

Without looking below to see the answer first, try to guess what each of these statements will produce as output:

And to slightly distract you from checking out the answers yet, here is another wise-looking owl who is thinking about the answers, and warning you not to look further down the page yet:

Anyway, here’s what happens when you run this T-SQL in SQL Server:

Surprised? I’d have to say that I was. Now as my buddy Adam Machanic pointed out, it’s not the fault of TRY_CAST and TRY_CONVERT because they just TRY to do a CAST and a CONVERT. And it’s the original functions that have the bizarre behavior.

Can’t say that I love this because it means that I can’t use these functions for their purpose, except for decimal. So that then left me wondering which types had this behavior.

Let’s find out! I executed the following:

Note that I excluded old data types, etc. that can’t cast to sql_variant anyway. And here’s the outcome:

So, you’ve been warned.

2017-12-04