Is IsDate function in SQL Server handling 201301 as YYYYMM or YYMMDD?

Someone reported this error on Microsoft Connect:

I came across the following error message on the Microsoft Connection website. The user reported that the function IsDate (Transact-SQL)checked format YYYYMMperiods for periods involving 2012, but stopped working from the beginning of 2013.

ISDate () returns different results since 2013

The user reported that the following is indicated as input. I have listed what the user expects 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 the MSDN website about IsDate (Transact-SQL). The documentation states the definition below, but not a single example is found that confirms that a function is being YYYYMMtested by a function.

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

datetime (Transact-SQL) , YYYYMM . , , datetime2.

script, , .

, :

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);

:

366 2012 . . 200229 IsDate, 200230, , IsDate YYMMDD YYYYMM .

Number  IsValidDate
------  -----------
200227        1
200228        1
200229        1
200301        1
200302        1
200303        1

:

  • , IsDate YYMMDD, YYYYMM?

  • YYMMDD , , . 1920 2020 - ? ?

  • . , . 00 09, ?

:

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

. , - Microsoft Connect.

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)
+5
3
  • ISDATE yymmdd.

  • . 2049, , < 50 *, 20xx, >= 50 - 19xx.

  • isdate .

+2

, isdate , ,

, 12 -

SELECT ISDATE('201201'),ISDATE('201301'),ISDATE('199901')

, 1999 0

, ,

SELECT ISDATE(CONVERT(DATETIME,0)),ISDATE(CONVERT(DATETIME,''))
+2

Based on your results, it seems that, for example, 201201or is 201301interpreted as YYMMDD. (And the date of the 13th month is considered invalid.)

+1
source

All Articles