SQL Server 2012 ISDATE() [duplicate]

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

Answer 1

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.