Does IsDate function in SQL Server treat 201301 as YYYYMM or YYMMDD?
sql-server, sql-server-2008, sql-server-2012
Solution
ISDATE is interpreting a six character string as `yymmdd`.
It is dependent on the configured cutoff Server Configuration Option. Default is `2049`, so years ending in `< 50`* is `20xx` and years `>= 50` is `19xx`.
Your test code is dealing with integers and is implicitly cast to a string when passed as an argument to `isdate` with no leading zeros.
Problem
Someone reported this bug on Microsoft Connect: I came across the following bug report at Microsoft Connection website. The user reported that `IsDate (Transact-SQL)` function was validating periods of the format `YYYYMM` for periods involving 2012 but stopped working since the beginning of 2013. ISDate() returns different results beginning in 2013 User has reported that the following as the input data. I have listed what user is expecting and the output from SQL Server. ``` SELECT ISDATE(201201) AS CheckDate1 --Expected: 1; Actual: 1 , ISDATE(201301) AS CheckDate2 --Expected: 1; Actual: 0 , ISDATE(201401) AS CheckDate3 --Expected: 1; Actual: 0 , ISDATE(20130101) AS CheckDate4 --Expected: 1; Actual: 1 ``` Read the MSDN documentation: I read the documentation on MSDN website about `ISDATE (Transact-SQL)`. The documentation states the below definition but couldn't find any examples that would verify `YYYYMM` is being validated by the function. ``` Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ``` On the definition about data type datetime (Transact-SQL), I could not find anything that would suggest `YYYYMM` is a valid date format. The value that the user specified is not of datetime2 format either. I wrote the below script to find out what is going on. Query attempt to find out what is going on: ``` DECLARE @StartAt INT; DECLARE @EndAt INT; SET @StartAt = 200000; SET @EndAt = 201312; ;WITH Numbers AS ( SELECT @StartAt AS n UNION ALL SELECT n + 1 FROM Numbers WHERE n < @EndAt ) SELECT n AS Number , ISDATE(n) AS IsValidDate FROM Numbers WHERE ISDATE(n) <> 0 OPTION (MAXRECURSION 10000); ``` Above query results: The above query returned 366 rows for each day in the year 2012. Here is a snapshot of the data between February and March. The value 200229 is considered valid by IsDate but not 200230, which makes me think that IsDate is treating these values of the format `YYMMDD` and not as `YYYYMM` as the user was expecting it. ``` Number IsValidDate ------ ----------- 200227 1 200228 1 200229 1 200301 1 200302 1 200303 1 ``` Questions: Am I correct that IsDate is treating the values mentioned above as `YYMMDD` and not as `YYYYMM`? If `YYMMDD` is indeed the format that the function is trying to evaluate, which century does it represent. Does it represent values of years `1920` or `2020` or something else? Or does it not matter? I replaced the start and end values in the above query with the below mentioned ones. I have also listed the number of valid rows found between those ranges. Why doesn't the years endings with 00 through 09 do not return any rows? Multiple attempts: ``` Start End Rows ------ ------ ---- 000000 001231 0 010000 011231 0 020000 021231 0 030000 031231 0 040000 041231 0 050000 051231 0 060000 061231 0 070000 071231 0 080000 081231 0 090000 091231 0 100000 101231 365 110000 111231 365 120000 121231 366 ... ... 980000 981231 365 990000 991231 365 ``` SQL Server version that I used to run the above query: I ran the above query on sql-server-2012 version. I have tagged this question under sql-server-2008 because the user reported this issue under that version on Microsoft Connect website. ``` Microsoft SQL Server 2012 - 11.0.2316.0 (X64) Apr 6 2012 03:20:55 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) ```