Why ISDATE() is returning 9996 as date?

Question: I'm using the latest version of SQL Server 2022 Enterprise edition with SSMS ver 20.

Why do all statements - except for the last line - return 1 as value? All my settings are default. I have made no changes to configuration.

select ISDATE(9996) --returns 1, why?
select ISDATE(8234)
select ISDATE(7234)
select ISDATE(6234)
select ISDATE(5234)
select ISDATE(4234)
select ISDATE(3234)
select ISDATE(2234)
select ISDATE(1234) --returns 0, as expected

Question 1:

Why does ISDATE( 9996 ) indicate that 9996 "is" a date?

Because ISDATE(9996) expects a character-string argument, so the integer value 9996 is implicitly converted to the string '9996', which is then interpreted as a year-number, which can be widened to a datetime value by assuming it's midnight on the January 1st of that year-number: so '9996' is interpreted as 9996-Jan-01, which is a real date - just one that's some 7,971 years into the future...

This implicit conversion is described in the documentation:

ISDATE( <expression> )

<expression> is a character string or expression that can be converted to a character string

---

Question 2:

Why does ISDATE( 2234 ) == 1, but ISDATE( 1234 ) == 0?

Come with me on a journey through history...

• The ISDATE() function is very old - I believe it was present in at least SQL Server 7.0 (released in 1998). Its purpose is/was to indicate if a character-string value could be parsed as a datetime value via CONVERT/CAST without causing a runtime error leading to an aborted query.
  • _{However, today we should always prefer TRY_CONVERT() while ISDATE() should be avoided because its behaviour varies based on the contextual SET DATEFORMAT and SET LANGUAGE options.}
• I'd like to clarify that despite having the name "ISDATE" it does not have anything to do with the date data-type: instead it's actually tied to the old-and-busted datetime data-type.
  • Y'see, the O.G. datetime datatype has a surprising limitation: it cannot be used to express dates (nor date+time values) before Jan 1st, 1753. (Whereas the date and datetime2(n) types support going back as far as Year 1 A.D.; this is one of many reasons why we should always prefer the datetime2(n) type over datetime).
    • Fun-fact: the date type was not added to SQL Server until as recently as 2008. _{(If you think that's crazy because that means you couldn't have a simple birthdate column - well, actually, yes, things really were that bad back then.It was even worse for Oracle users: Oracle never had a bit/bool type until very recently, despite being the leading RDBMS for thirty years - people just had to suck-it-up and figure out alternatives)}
• ...which is to say that ISDATE() only returns true for expressions corresponding to dates between 1753-Jan-01 and 9999-Dec-31.

...accordingly, 2234 is a valid year (as is 9996, 8234, etc), but 1234 is not a valid year because it predates 1753.

Proof:

SELECT
    t.int_arg,
    v.is_date,
    v.is_date_chr,
    v.as_date,
    v.as_old_and_horrible_datetime
FROM ( 
    VALUES  (10000), (9999) ,(9996), (8234), (7234), (6234), (5234), (4234)
    , (3234), (2234), (1754), (1753), (1752), (1751), (1234)
) t (int_arg)
CROSS APPLY (
    SELECT  ISDATE(int_arg) AS is_date
    ,   ISDATE(CAST(int_arg AS VARCHAR(30))) AS is_date_chr
    ,   TRY_CONVERT(date, CAST(int_arg AS VARCHAR(30))) AS as_date
    ,   TRY_CONVERT(datetime, CAST(int_arg AS VARCHAR(30))) AS as_old_and_horrible_datetime
    ) v

...has these results - note the transition between 1753 and 1752:

---

And here's a DB-Fiddle for good measure.