What is the Biggest Limitation of ISDATE() Function? – Interview Question of the Week #137

Question: What is a serious limitation of ISDATE() in SQL Server?

Answer: I wish the interview question had been phrased that way. In the meeting, the candidate was actually shown two statements and asked why one returned 1 and the other 0. That tests whether someone remembers a particular boundary, when I would rather give them room to reason about the data type.

SET DATEFORMAT ymd;
SELECT ISDATE('6666-04-30') AS IsValidDate;
SELECT ISDATE('1111-04-30') AS IsValidDate;

The session uses year-month-day interpretation for these strings. These are the original SQL Server results from the article. The first returns 1; the second returns 0.

The crucial word in the function’s definition is datetime. ISDATE asks whether its input is valid as the older datetime type. That type begins at 1753-01-01. The date and datetime2 types begin at 0001-01-01. So the year 1111 is a valid date, yet ISDATE rejects it. My original explanation said the boundary was 1582; that was incorrect for SQL Server datetime.

When the destination type is date, validate conversion to that actual type. Here style 23 specifies the year-month-day text format:

SELECT
    ISDATE('1111-04-30') AS IsValidDatetime,
    TRY_CONVERT(date, '1111-04-30', 23) AS ValidDate,
    TRY_CONVERT(datetime, '1111-04-30', 23) AS InvalidDatetime;

The expected result is 0, 1111-04-30, and NULL, respectively. The returned NULL means the requested datetime conversion failed; it does not make the calendar date invalid.

There is a second limitation worth discussing with a candidate. Ambiguous strings, especially two-digit years, can be interpreted according to language, date-format, and cutoff settings. A typed input or a documented four-digit format is a stronger contract than a guess about a short date string. ISDATE also does not validate the full precision of a datetime2 value.

I would prefer to ask: “Which type will store this value, and how would you test conversion to it?” That lets the candidate demonstrate the reasoning that matters in a real database. What would you ask instead?

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.