String or binary data would be truncated: Error not caught by TRY_CAST

user_204900 0
Viewed 411

The query below...

SELECT YEAR(TRY_CAST(m.MetaValue AS DATE))
FROM MetaData m

...results in this error:

String or binary data would be truncated.

If I filter the query to a valid recordset, the error goes away:

SELECT YEAR(TRY_CAST(m.MetaValue AS DATE))
FROM MetaData m
WHERE m.MetaKey IN (...)

If I try to filter based on the selected expression, the query fails again:

SELECT * FROM (
    SELECT YEAR(TRY_CAST(m.MetaValue AS DATE)) AS Yr
    FROM MetaData m
    WHERE MetaKey IN (...)
) x
WHERE x.Yr > 1900

What's going on here? I get the sense that SQL Server is optimizing the query into something along these lines, but I don't know how to prevent it:

SELECT YEAR(TRY_CAST(m.MetaValue AS DATE)) AS Yr
FROM MetaData m
WHERE MetaKey IN (...)
AND YEAR(TRY_CAST(m.MetaValue AS DATE)) > 1900
sql-server
1 Answers

You get this issue if MetaValue is > 8,000 bytes. It throws an error not caught by the TRY_CAST.

A simple demo of this is below (db <> fiddle)

DECLARE @X NVARCHAR(MAX) = 'X'
SET @X = REPLICATE(@X, 4001)

SELECT TRY_CAST(@X AS DATE)

In general SQL Server can evaluate expressions out of logical processing order causing the potential for this even if all the ones matching the WHERE MetaKey IN (...) condition are short strings (also warned about here).

Guarding the TRY_CAST in an additional CASE expression should resolve things in this case

SELECT CASE WHEN DATALENGTH(m.MetaValue) <8000 THEN YEAR(TRY_CAST(m.MetaValue AS DATE)) END
FROM MetaData m
WHERE m.MetaKey IN (...)
user_732260
Related