SQL: Use TRY_CAST instead of ISNUMERIC and ISDATE

Like most developers, I often need to check if a string value is a valid number or a valid date (or datetime). In T-SQL, the functions provided for this are ISNUMERIC and ISDATE. These are pretty generic functions though, and I almost never use them now. I believe you’re now much better off using TRY_CAST instead.

“Numeric” is a fairly vague concept. Which type of number are we checking for?

“Date” is also vague in this context. The ISDATE function says it checks for date, time, or datetime.

Most of the time, what I’m really asking is “Can I safely convert this value to an integer?” or “Can I safely convert this value to a date?”, etc.

Ever since we’ve had TRY_CAST, I think you’re better off using it to attempt the operation and fail silently. Use TRY_CONVERT if you want to check using a style, and TRY_PARSE if you want to check a string using a different cultural setting than the one in your current session.

For example with “Can I safely convert this value to an integer?”,

instead of writing:

ISNUMERIC(value) = 1

write this:

TRY_CAST(value AS int) IS NOT NULL

At least then you can specify the precise data type that you’re checking for.

2021-02-23