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.