September 13, 2012 at 10:47 am
Hi All, i have come across an issue in PROD today.
then i found the below Case 1 was the issue.
but, when i about the fix it, i am bit confused.
--Case 1:
--Today's PROD problem State. This was the error.
set dateformat ymd
select datepart(dd,'13/09/2012')
Rest of the cases are from my analysis
--Case 2:
--I doubt '2012' cant be recognized as date part, so i tried with
--a valid date '30'. fine, returning 30
set dateformat ymd
select datepart(dd,'13/09/30')
--Case 3:
--Then i thought of changing the "dateformat".
--this is also fine, returning '13' as date part as intended.
set dateformat dmy
select datepart(dd,'13/09/2012')
--Case 4:
--BUT, surprise is 'how it worked till yesterday'
--So, i give yesterday's(date=12) State. i expected this should give
--same error of Case 1, which i confirmed by Case 2.
--but, it returns 9 for Date. it recognised as 'mdy' format
set dateformat ymd
select datepart(dd,'12/09/2012')
So, My questions are
1. is this implicit date format change is an expected behaviour
2. if this could happen yesterday (12), why not today (13).
Appreciating your help
September 13, 2012 at 11:41 am
I just played around with this a bit.
Per MSDN (http://msdn.microsoft.com/en-us/library/ms189491.aspx):
Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.
It looks like the language setting is overriding the DateFormat setting in these cases. It's reading it as MM/DD/YYYY, regardless of the DateFormat instructions. That's based on the results, since the documentation (same location) says:
SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.
However, per the MSDN information on string date-formats (http://msdn.microsoft.com/en-us/library/ms180878(v=sql.105).aspx), '12/09/2012' should be unaffected by DateFormat only if it's combined with a time value, and should be affected by it if it is not. However, the sample code right below the chart on that seems to contradict the chart, and predicts the behavior you and I are seeing.
So, MM/DD/YYYY format seems to be independent of DateFormat, so you're getting 9 as the date, and 13 as the month, in your code. Yesterday, you were getting 12 as the month, so it didn't throw an error.
The recommended practice is use YYYY-MM-DD format for dates as strings. You can ommit the punctuation if you like, and use 20120913 instead of 2012-09-13. SQL Server will read either one correctly.
- 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 13, 2012 at 1:38 pm
GSquared (9/13/2012)
I just played around with this a bit.Per MSDN (http://msdn.microsoft.com/en-us/library/ms189491.aspx):
Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.
It looks like the language setting is overriding the DateFormat setting in these cases. It's reading it as MM/DD/YYYY, regardless of the DateFormat instructions. That's based on the results, since the documentation (same location) says:
SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.
However, per the MSDN information on string date-formats (http://msdn.microsoft.com/en-us/library/ms180878(v=sql.105).aspx), '12/09/2012' should be unaffected by DateFormat only if it's combined with a time value, and should be affected by it if it is not. However, the sample code right below the chart on that seems to contradict the chart, and predicts the behavior you and I are seeing.
So, MM/DD/YYYY format seems to be independent of DateFormat, so you're getting 9 as the date, and 13 as the month, in your code. Yesterday, you were getting 12 as the month, so it didn't throw an error.
The recommended practice is use YYYY-MM-DD format for dates as strings. You can ommit the punctuation if you like, and use 20120913 instead of 2012-09-13. SQL Server will read either one correctly.
Thanks for your time.
1. your comment "language setting is overriding the DateFormat"
looks contradicting with MSDN that "DATEFORMAT overrides SET LANGUAGE"
2. reg. your statement "MM/DD/YYYY format seems to be independent of DateFormat"
sorry, this looks not true partially, i found some below cases which respects DATEFORMAT
set dateformat dmy
select datepart(dd,'09/12/2012') -- returns 9
set dateformat mdy
select datepart(dd,'09/12/2012') -- returns 12
set dateformat myd
select datepart(dd,'09/12/2012') -- error
3. But, Finally, I understood a solution that it is good to use '19980223' format always
which will eliminate the use of DATEFORMAT and it's confusion.
September 14, 2012 at 6:10 am
Yeah. You found the same thing with the documentation that I did. It seems to contradict itself.
- 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 14, 2012 at 6:49 am
Rather than trying to navigate all the quirks of the interpretation of strings as dates, use an explicit style:
-- Style 103 = dd/mm/yyyy
SELECT DATEPART(DAY, CONVERT(date, '13/09/2012', 103));
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2012 at 8:15 am
SQL Kiwi (9/14/2012)
Rather than trying to navigate all the quirks of the interpretation of strings as dates, use an explicit style:
-- Style 103 = dd/mm/yyyy
SELECT DATEPART(DAY, CONVERT(date, '13/09/2012', 103));
Or ANSI/ISO standard, as already mentioned. '2012-09-13' won't generate a problem no matter how you have the language and dateformat settings.
- 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 14, 2012 at 8:19 am
GSquared (9/14/2012)
SQL Kiwi (9/14/2012)
Rather than trying to navigate all the quirks of the interpretation of strings as dates, use an explicit style:
-- Style 103 = dd/mm/yyyy
SELECT DATEPART(DAY, CONVERT(date, '13/09/2012', 103));
Or ANSI/ISO standard, as already mentioned. '2012-09-13' won't generate a problem no matter how you have the language and dateformat settings.
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2012-07-18');
versus:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2012-07-18', 121);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2012 at 8:26 am
SQL Kiwi (9/14/2012)
GSquared (9/14/2012)
SQL Kiwi (9/14/2012)
Rather than trying to navigate all the quirks of the interpretation of strings as dates, use an explicit style:
-- Style 103 = dd/mm/yyyy
SELECT DATEPART(DAY, CONVERT(date, '13/09/2012', 103));
Or ANSI/ISO standard, as already mentioned. '2012-09-13' won't generate a problem no matter how you have the language and dateformat settings.
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2012-07-18');
versus:
SET LANGUAGE FRENCH;SELECT CONVERT(DATETIME, '2012-07-18', 121);
Yep. I forgot it needs to have the hyphens removed.
- 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 14, 2012 at 8:32 am
GSquared (9/14/2012)
Yep. I forgot it needs to have the hyphens removed.
Yes. AFAIK, the 'yyyymmdd' format is always OK. I personally try to use a CONVERT with an explicit style anyway though, it just feels better somehow. Weird things with implicit conversions have bitten me so many times I suppose I am quite paranoid about types these days.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2012 at 9:04 am
SQL Kiwi (9/14/2012)
GSquared (9/14/2012)
Yep. I forgot it needs to have the hyphens removed.Yes. AFAIK, the 'yyyymmdd' format is always OK. I personally try to use a CONVERT with an explicit style anyway though, it just feels better somehow. Weird things with implicit conversions have bitten me so many times I suppose I am quite paranoid about types these days.
I've been using YYYYMMDD (no punctuation) since the early '80s, so it's my go-to on date strings.
In folder/file names, it keeps things sorted in date order, where the sorting is ASCII-sequence.
DateTime, I'm so used to YYYYMMDDTHHMMSS for the same reason, that I sight-read it more easily than many other more "human" formats.
Leaving the hyphens in was actually the exception for me. And it made me make a mistake. Funny how Murphy can bite that way so easily.
- 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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply