Why function return 1

Pankaj Kumar Choudhary

11y969

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

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..........

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir11y ago
Accepted 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 Choudhary11y ago
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 Choudhary11y ago
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 Shahi11y ago
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 Choudhary11y ago
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 Shahi11y ago
SET LANGUAGE us_english;
SET DATEFORMAT dmy;
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')

its return 0
0
0
0
0
Upendra Pratap Shahi11y ago
This is correct Pankaj Kumar Choudhary , ISDATE() only check the dateformat so it is right dear.