SQL Server 2012 ISDATE()

Question

The MS doc states that ISDATE()

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0

So why is it returning 0 in the example below?

DECLARE @DT VARCHAR(30) = '1/4/1752'

SELECT 
    ISDATE(@DT),
    TRY_CONVERT(DATE, @DT, 101),
    TRY_CONVERT(DATETIME, @DT),
    TRY_CAST(@DT as DATE),  
    TRY_CAST(@DT AS DATETIME)

returns

0   1752-01-04  NULL    1752-01-04  NULL

Change the date to 1753 and ...

1   1753-01-04  1753-01-04 00:00:00.000 1753-01-04  1753-01-04 00:00:00.000

select ISDATE('17521231'), ISDATE('17530101') gives

0   1

Note that `17520401` *is* a valid `DATETIME2` value, but *not* a valid `DATETIME` value, so `ISDATE` still returns 0. The `TRY_CONVERT` approach as suggested by Gordon will cover this case correctly too. (And the documentation probably needs tweaking -- `17520401` is a valid `DATE`, but not, apparently, "a valid date value", which is wrong.) The behavior of `ISDATE` itself likely will not be changed due to backwards compatibility concerns. — Jeroen Mostert, Dec 05 '16 at 11:47
I think this is not a duplicate ,documentation states `range for datetime data is 1753-01-01 through 9999-12-31, while the range for date data is 0001-01-01 through 9999-12-31.` and it returns `Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.` — TheGameiswar, Dec 05 '16 at 12:02
I suggest opening an issue on Microsoft Connect for this, asking them to either change the behavior of `ISDATE` or amend the documentation to make it clear that `ISDATE` does not cover the extended range of `DATE`. — Jeroen Mostert, Dec 05 '16 at 13:21
@Jeroen, thanks for reading the question. It will just remain one of those anomalies! — Maurice1408, Dec 05 '16 at 13:58

Gordon Linoff · Answer 1 · 2016-12-05T12:35:55.040

13

As explained in the documentation, the earliest datetime value is '1753-01-01'.

I would suggest that you use try_convert() instead. This gives you more flexibility:

 try_convert(date, '17521231') is not null

The date data type goes back to year one.

edited Dec 05 '16 at 12:35

answered Dec 05 '16 at 11:46

Gordon Linoff

1,242,037
58
646
786

3

The documentation also explains that the earliest date is "while the range for date data is 0001-01-01 through 9999-12-31" ans ISDATE() doc says "Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0." so surely ISATE('17520101') is a valid date? – Maurice1408 Dec 05 '16 at 11:52
there wasn't a year zero! – Cato Dec 05 '16 at 11:52

SQL Server 2012 ISDATE()

1 Answers1