T-SQL 101: 83 Determining if a string is a number or date with ISNUMERIC and ISDATE

Sometimes we need to determine whether a string is a date or whether it is a number.

In the first example above, I’m asking if the string ‘20190229’ is a valid date. You can see from the response (0) that it isn’t. That’s because even though it’s a valid date format, February in 2019 doesn’t have a 29th day. It’s not a leap year.

The value returned from the ISDATE function is a zero or a 1.  Curiously, the return value is of data type int. You’d think that a function that starts with Is and tests something would return a bit data type instead. But that’s just one of the curiosities of T-SQL.

In the second example, I’m asking if the string ‘hello’ is a date. Clearly it’s not, so the return value is 0. And in the third example, ‘20190228’ is a date.

Similarly, we might also need to check if a string is a valid number. The ISNUMERIC function is used to do that.

However, that function might work a bit differently to what you’d expect. ISNUMERIC returns 1 for some strings that contain values that aren’t numbers like plus (+), minus (-), and currency symbols like ($).

If that’s not suitable for you, you might need to use a different function. A TRY_CAST or TRY_COVERT might be better, depending upon what you’re trying to achieve. I’ll talk about them soon in an upcoming blog post.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-02-01