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:

SELECT TRY_CAST('' AS int);
SELECT TRY_CAST('    ' AS int);
SELECT TRY_CAST('' AS date);
SELECT TRY_CAST('' AS decimal(18, 2));
SELECT TRY_CONVERT(date, '', 103);

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, not the TRY versions of them.

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:

USE tempdb;
GO

SET NOCOUNT ON;

DECLARE @TypeName sysname;
DECLARE @SQL nvarchar(max);
DECLARE @Outcomes TABLE
(
    OutcomeID int IDENTITY(1,1) PRIMARY KEY,
    TypeName sysname,
    ReturnedValue sql_variant
);

DECLARE TypeList CURSOR FAST_FORWARD READ_ONLY
FOR
SELECt typ.[name] AS TypeName
FROM sys.types AS typ
WHERE typ.system_type_id = typ.user_type_id
AND typ.[name] NOT IN (N'image', N'json', N'text', N'ntext', N'timestamp', N'xml')
ORDER BY TypeName;

OPEN TypeList;
FETCH NEXT FROM TypeList INTO @TypeName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'SELECT ''' + @TypeName + N''', TRY_CAST('''' AS ' + @TypeName + N');';
    INSERT @Outcomes (TypeName, ReturnedValue)
    EXEC(@SQL);

    FETCH NEXT FROM TypeList INTO @TypeName;
END;
CLOSE TypeList;
DEALLOCATE TypeList;

SELECT * FROM @Outcomes ORDER BY TypeName;

Note that I excluded old data types, and others that can’t cast to sql_variant anyway. (Mind you, no idea why XML and JSON can’t be cast to sql_variant). And here’s the outcome:

So, you’ve been warned.

2026-08-12