Home Dashboard Directory Help
Search

ISDate() returns different results beginning in 2013 by James E. Freedle II


Status: 

Closed
 as By Design Help for as By Design


1
5
Sign in
to vote
Type: Bug
ID: 779223
Opened: 2/14/2013 8:34:08 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

The BI Team has been using IsDate with YYYYMM formats to verify valid dates. In years prior to 2013 this would return 1 while dates after 2013 have thusfar returned 0.
Details
Sign in to post a comment.
Posted by James E. Freedle II on 2/18/2013 at 11:53 AM
I had forgotten about the lazy two digit formatting as I have not used that myself since the early 1990's. That does make sense that it worked because it was interepting it differently than what the user meant. This does seem more like a user error than a bug.
Posted by Scott R. 007 on 2/16/2013 at 12:36 AM
Building on the other replies, I confirmed that this behavior has been consistently in place since at least SQL 2000 all the way through the current SQL 2012. It is not new behavior.

Six-digit dates without date part delimiters are correctly being validated by the IsDate function as YYMMDD and not as YYYYMM, so the example dates 201301 and 201401 are properly flagged as invalid (month 13 or 14 is invalid). The stated correct date of 201201 is being flagged correct not because it is interpreted as 2012-01 (YYYYMM), but because it is interpreted as 20-12-01 (YYMMDD).

This understanding can be confirmed by CASTing the valid example dates to the datetime data type and observe the results, and by observing that a CAST of the invalid example values fails with an error.

Work-around: Use the IsDate function to validate input values in YYYYMM format by adding 01 to the end (for day of month MM = 01) - as shown in fourth example of reported problem.

A misunderstanding of an ambiguous date string - yes. A bug / product defect - no.
Posted by esivasankar on 2/14/2013 at 1:41 PM
I don't think this is a bug. Please read the below link for what I found with respect to this issue. IsDate() function seem to validate dates of format YYMMDD and not YYMM as you were expecting it to.

http://stackoverflow.com/questions/14884307/does-isdate-function-in-sql-server-validate-periods-of-format-yyyymm
Posted by SQL_Menace on 2/14/2013 at 1:32 PM
This is not a bug, if they were running this in 1999 it would also blow up. SELECT ISDATE('201201'),ISDATE('201301'),ISDATE('199901')
Sign in to post a workaround.