August 30, 2011 at 6:14 am
Recently i migrated from SQL Server 2005 to SQL Server 2008. At one place our code was giving error. After few minutes of testing I found the issue was because of ISDATE funciton. In 2005, it was returing 1 while in 2008 the same value returned 0. Why is this different behaviour if I am sending a date value like say '01/01/2011' or '01-01-2011' etc ?
August 30, 2011 at 6:22 am
Have you changed the default language of the server instance?
I checked, and both of the date formats you listed return 1 when I try them in SQL 2008 R2, using English default and all that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 6:24 am
sqlnaive (8/30/2011)
Why is this different behaviour if I am sending a date value like say '01/01/2011' or '01-01-2011' etc ?
Both the value is giving 1 in SQL Server 2008 (SP2)
Regards - Deepak
August 30, 2011 at 7:07 am
Is there a way to set it serverwide or database wide ? Or i'll have to put setr dateformat statement in my every procedure/query ?
August 30, 2011 at 7:17 am
Both you guys said same thing. My question is the result varying in sql 2005 and 2008.
select isdate('01/01/2011') or isdate('01-01-2011')
-- giving 1 in sql server 2008
select isdate('01/01/2011') or isdate('01-01-2011')
-- giving 0 in sql server 2005
August 30, 2011 at 7:28 am
sqlnaive (8/30/2011)
Both you guys said same thing. My question is the result varying in sql 2005 and 2008.select isdate('01/01/2011') or isdate('01-01-2011')
-- giving 1 in sql server 2008
select isdate('01/01/2011') or isdate('01-01-2011')
-- giving 0 in sql server 2005
Ignoring for the moment that that's the exact opposite of your original post, I get 1 and 1 for both of those in SQL 2005.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 7:37 am
i prefer you to go through this link ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/8e2c9ee7-388a-432f-b2c9-7b398f26bf85.htm#SessionSettingDependencies
Regards - Deepak
August 30, 2011 at 7:44 am
What is the output of these statements when you run them on each server?
select
DT1 = isdate('01/01/2011'),
DT2 = isdate('01-01-2011'),
SQLVersion = convert(varchar(20),serverproperty ('ProductVersion'))
dbcc useroptions with no_infomsgs
August 30, 2011 at 8:03 am
GSquared, I mentioned "In 2005, it was returing 1 while in 2008 the same value returned 0"
August 30, 2011 at 8:18 am
sqlnaive (8/30/2011)
GSquared, I mentioned "In 2005, it was returing 1 while in 2008 the same value returned 0"
You said the exact opposite in your third post.
Can you please post the query results that I asked you for?
August 30, 2011 at 8:40 am
Thanks Michael for the query. The query giving same result for the SELECT. however difference shows in result for DBCC command. Dateformat is shown as dmy and mdy as well as language is British and us_english, respectively for 2005 and 2008 environment.
August 31, 2011 at 8:37 am
sqlnaive (8/30/2011)
GSquared, I mentioned "In 2005, it was returing 1 while in 2008 the same value returned 0"
Your first post said, "In 2005, it was returing 1 while in 2008 the same value returned 0."
The one I said was the opposite had it the other way around, 1 in 2008 and 0 in 2005.
Not a big deal, but does make it a little more difficult to resolve the issue.
Back on the main topic, setting mdy vs dmy will make a difference for IsDate on many dates, but not on 1/1/11 (or variations of that), since 1/1 is 1st January, whether the month is first or second.
Given the confusion on which one does which result, please confirm that 1/1/11 gives different results on each server. If so, that means it's something other than the language setting. Not sure what yet, but we'll figure it out or MS support will.
If 1/1/11 gives 1 on both, please check 1/31/11 vs 31/1/11 on each. If 1/1/11 works on both, and 1/31/11 gives 1 on one and 0 on the other, then it's the language setting that's causing the problem. That's by far the most likely scenario, which is why I asked about it in my first post here. Language settings can also do some interesting (and often frustrating) things to numbers with periods and commas in them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2011 at 8:37 am
I ran following command on both servers:
select isdate('1/1/11')-- Gives 1 on both servers
select isdate('31/1/11')-- Gives 0 on 2008 but 1 in 2005
select isdate('1/31/11')-- Gives 1 on 2008 but 0 in 2005
So its purely a case of default language setting. Will it be difficult to change that ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply