Why function return 1

Question

Hello Friend i was doing some exercise on date time function and face a problem please Help me to solved out this problem

my query is

SET LANGUAGE us_english;
SET DATEFORMAT mdy;

SELECT ISDATE('03/18/2010') AS [ISDATE] UNION ALL
SELECT ISDATE('03-15-2010') UNION ALL
SELECT ISDATE('03.15.2010') UNION ALL
SELECT ISDATE('03/2010/15')

when we execute this query it will return

1

Now my question is last select statement is

SELECT ISDATE('03/2010/15')

format of date is MYD. But i define the format of date MDY

So according this server should be return 0 instead of 1 but it doesn't return 0.

So Please explain me what is Reason behind it...........

Thanks for reading my Question..........

Manoj Bhoir · Answer

Hi 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 check the default language option which allows dates in a format like MM/yyyy/dd.

Pankaj Kumar Choudhary · Answer

Thanks You sir. I don't know how ISDATE depends on the settings set by SET DATEFORMAT SET LANGUAGE and default language option. But This is Correct that ISDATE depends on the settings set by SET DATEFORMAT SET LANGUAGE and default language option.

Pankaj Kumar Choudhary · Answer

yes Sir Your query is correct that SET DATEFORMAT mdy; SELECT ISDATE('2008/15/04'); --Returns 0. SELECT ISDATE('04/15/2008'); --Returns 1. SET DATEFORMAT ydm; SELECT ISDATE('2008/15/04'); --Returns 1. But if make a little change in your query then it will give incorrect answer SET DATEFORMAT mdy; SELECT ISDATE('04/2008/4'); --Returns 1. // Why this statement return 1 while DATEFORMAT of date is mdy and we are providing a date of format MYD Please Explain what is reason behind this SELECT ISDATE('04/15/2008'); --Returns 1.

Upendra Pratap Shahi · Answer

SET DATEFORMAT mdy; SELECT ISDATE('2008/15/04'); --Returns 0. SELECT ISDATE('04/15/2008'); --Returns 1. SET DATEFORMAT ydm; SELECT ISDATE('2008/15/04'); --Returns 1. check format and not match then return 0(false) and match return 1(true)

Pankaj Kumar Choudhary · Answer

Sorry Sir i am not satisfied with your argument that ISDATE() only check the dateformat Let us Take an Example: SET DATEFORMAT mdy; SELECT ISDATE('2008/15/04'); --Returns 0. SET DATEFORMAT ydm; SELECT ISDATE('2008/15/04'); --Returns 1. Now can you explain why both query give different answer for same input.......

Upendra Pratap Shahi · Answer

SET LANGUAGE us_english;
SET DATEFORMAT mdy;

SELECT ISDATE('03/18/2010') AS [ISDATE] UNION ALL
SELECT ISDATE('03-15-2010') UNION ALL
SELECT ISDATE('03.15.2010') UNION ALL
SELECT ISDATE('03/2010/15')

Upendra Pratap Shahi · Answer

This is correct Pankaj Kumar Choudhary  ISDATE() only check the dateformat so it is right dear.