sql server - Why ISDATE() is returning 9996 as date? - Stack Overflow

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: 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

• sql-server
• t-sql
• sql-server-2022

Share Follow edited Jan 31 at 4:43 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 31 at 1:19 namnam 24k4545 gold badges189189 silver badges403403 bronze badges 5

• 5 Cos its flawed... just like isnumeric... – Dale K Commented Jan 31 at 1:25
• 1 Or TRY_CAST(9996 AS DATE) – Yitzhak Khabinsky Commented Jan 31 at 1:41
• 4 The behavior of ISDATE() depends on your current SET DATEFORMAT and SET LANGUAGE options - please share those. Alternatively, only use ISDATE() with CONVERT with the style parameter set. – Dai Commented Jan 31 at 1:46
• If you really need to parse, use TRY_PARSE instead of the old IS... methods. It's far better to clean up the types before importing them to the database though – Panagiotis Kanavos Commented Jan 31 at 8:49
• It would be a lot better to store strongly typed date values though, parsing them using any client-side language. SQL is bad at text processing and T-SQL even more so. Regular expressions are still a promised feature for SQL Server 2025, when MySQL and PostgreSQL have them for quite some time. – Panagiotis Kanavos Commented Jan 31 at 9:38

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 10

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:

int_arg	is_date	is_date_chr	as_date	as_old_and_horrible_datetime
10000	0	0	NULL	NULL
9999	1	1	9999-01-01	9999-01-01 00:00:00.000
9996	1	1	9996-01-01	9996-01-01 00:00:00.000
8234	1	1	8234-01-01	8234-01-01 00:00:00.000
7234	1	1	7234-01-01	7234-01-01 00:00:00.000
6234	1	1	6234-01-01	6234-01-01 00:00:00.000
5234	1	1	5234-01-01	5234-01-01 00:00:00.000
4234	1	1	4234-01-01	4234-01-01 00:00:00.000
3234	1	1	3234-01-01	3234-01-01 00:00:00.000
2234	1	1	2234-01-01	2234-01-01 00:00:00.000
1754	1	1	1754-01-01	1754-01-01 00:00:00.000
1753	1	1	1753-01-01	1753-01-01 00:00:00.000
1752	0	0	1752-01-01	NULL
1751	0	0	1751-01-01	NULL
1234	0	0	1234-01-01	NULL

---

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