SQL Server ISDATE() Function - Can someone explain this?

Question

So I was looking at the documentation for the ISDATE() function in SQL Server and saw this in the examples:

SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.

The last example returns 1 (a valid date) but the date format above doesn't match the format in the expression of the function. I thought it was a mistake in the documentation but then curiously tried it out myself and it does actually return 1.

So why is '2008/04/15' a valid date when the date format is mdy?

Documentation here: http://msdn.microsoft.com/en-us/library/ms187347(SQL.105).aspx

Answer 1

If we set DateFormat to mdy then it will validate true to all dates in this format.

If it found invalid date format as per mdy then it check for setting according to Language. If found valid date as per supported by language then returns true else false.

if user A has a default language of us_english, then a date of 4/6/2006 will be interpreted as April 6, 2006. If user B has a default language of 'British', then the date will be interpreted as June 4, 2006.

/* Use these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
/* Expression in mdy dateformat */
SELECT ISDATE('04/15/2008'); --Returns 1.
/* Expression in mdy dateformat */
SELECT ISDATE('04-15-2008'); --Returns 1. 
/* Expression in mdy dateformat */
SELECT ISDATE('04.15.2008'); --Returns 1. 
/* Expression in myd  dateformat */
SELECT ISDATE('04/2008/15'); --Returns 1.

SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.

SET DATEFORMAT dmy;
SELECT ISDATE('15/04/2008'); --Returns 1.
SET DATEFORMAT dym;
SELECT ISDATE('15/2008/04'); --Returns 1.
SET DATEFORMAT ydm;
SELECT ISDATE('2008/15/04'); --Returns 1.
SET DATEFORMAT ymd;
SELECT ISDATE('2008/04/15'); --Returns 1.

SET LANGUAGE English;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET LANGUAGE Hungarian;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET LANGUAGE Swedish;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET LANGUAGE Italian;
SELECT ISDATE('2008/04/15'); --Returns 1.

/* Return to these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;

The Numeric format can use dash (-), dot (.) or slash (/) as separator. The rules for how SQL Server parses the string doesn't change depending on the separator. A common misconception is that the ANSI SQL format (sometime a bit incorrectly referred to as the "ISO format"), 1998-02-23, is language neutral for smalldatetime and datetime. It isn't. It is a numeric format and hence it is dependent on the SET DATEFORMAT and SET LANGUAGE setting:

SET LANGUAGE us_english 
SELECT CAST('2003-02-28' AS datetime) 
-----------------------
2003-02-28 00:00:00.000

SET LANGUAGE british 
SELECT CAST('2003-02-28' AS datetime) 
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.

Answer 2

from http://msdn.microsoft.com/en-us/library/ms187347%28SQL.105%29.aspx#SessionSettingDependencies

The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and default language option.

So if the given string not applies to the set dateformat it also cecks the default language option which allows dates in a format like y/m/d