January 8, 2009 at 1:45 pm
Hello everyone,
Ok this is weird. When I run the statement below it is returning 1. Can someone explain to me why this is returning 1?
select datediff(year, '2008-01-09 00:00', '2009-01-08 00:00')
This should return 0, but it's returning 1 and I can't figure out why.
Thanks,
Strick
January 8, 2009 at 1:56 pm
Why would you think the value should be 0? The number of year boundaries being crossed is 1 - therefore the difference is 1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 2:10 pm
Because the first date is in 2008 and the second one is in 2009.
- 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
January 8, 2009 at 2:20 pm
stricknyn (1/8/2009)
Hello everyone,Ok this is weird. When I run the statement below it is returning 1. Can someone explain to me why this is returning 1?
select datediff(year, '2008-01-09 00:00', '2009-01-08 00:00')
This should return 0, but it's returning 1 and I can't figure out why.
Thanks,
Strick
Why would you want to make a statement like this with ambiguous dates? If I run your code here in the UK, what dates will those implicit conversions return? Datediff expects datetime parameters.
Try this using datetime variables, and with month as well as year.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 2:33 pm
YYYY-MM-DD HH:MM isn't an implicit conversion that's localization dependent, so far as I know. The problem is almost certainly a typo in the first year. I think the post was meant to have 2009 in both years, but one of them is 2008.
- 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
January 8, 2009 at 2:39 pm
Chris Morris (1/8/2009)
stricknyn (1/8/2009)
select datediff(year, '2008-01-09 00:00', '2009-01-08 00:00')Why would you want to make a statement like this with ambiguous dates? If I run your code here in the UK, what dates will those implicit conversions return? Datediff expects datetime parameters.
Hmm. It has always been my understanding that "YYYY-MM-DD" is an unambiguous text date format, because there is not supposed to be any country that has "YYYY-DD-MM" as the standard. There sure are a lot of folks (me included) who are under that impression.
Also, I believe that '{date}' is supposed to be the official way to enter date constants in SQL Server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 2:51 pm
OK, I checked and yes, "YYYY-MM-DD" is the ISO 8601 standard for "unambiguous numerical text date format". (see here: http://www.uic.edu/depts/accc/software/isodates/index.html).
Also, as to the representation of date constants, here is what BOL says:
datetime constants
datetime constants are represented by using character date values in specific formats, enclosed in single quotation marks. For more information about the formats for datetime constants, see Using Date and Time Data.
So unless I am missing something, then I think that the OP is doing that part correctly. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 3:13 pm
RBarryYoung (1/8/2009)
OK, I checked and yes, "YYYY-MM-DD" is the ISO 8601 standard for "unambiguous numerical text date format". (see here: http://www.uic.edu/depts/accc/software/isodates/index.html).Also, as to the representation of date constants, here is what BOL says:
datetime constants
datetime constants are represented by using character date values in specific formats, enclosed in single quotation marks. For more information about the formats for datetime constants, see Using Date and Time Data.
So unless I am missing something, then I think that the OP is doing that part correctly. 🙂
Blimey...
SET DATEFORMAT YDM
SELECT DATEDIFF(MM,'2008-30-01 00:00', '2009-29-01 00:00')
--RESULT 12
SET DATEFORMAT YMD
SELECT DATEDIFF(MM,'2008-30-01 00:00', '2009-29-01 00:00')
--Server: Msg 242, Level 16, State 3, Line 9
--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SET DATEFORMAT YDM
SELECT DATEDIFF(MM,'2008-01-30 00:00', '2009-01-29 00:00')
--Server: Msg 242, Level 16, State 3, Line 2
--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SET DATEFORMAT YMD
SELECT DATEDIFF(MM,'2008-01-30 00:00', '2009-01-29 00:00')
--RESULT 12
Don't believe everything you read;)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2009 at 3:20 pm
Yeah, but "YDM" is not the date standard for any nation. That's why ISO choose it. And what is the alternative way to do this that is not either ambiguous or invalid under some possible dateformat/nationality setting?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 3:27 pm
The only way to completely avoid possible issues with this is to start with a datetime variable set to 0, then use dateadd year, dateadd month and dateadd day to build the date. Add in hours, minutes, et al, till the thermometer thingy pops.
Anything else is going to be dependent on settings.
Even the proposed solution by the guy with the problem about dates won't work, because assigning it to a variable from a string is just adding a step, it doesn't actually do what he wants.
Of course, there is another way, which would be to calculate the numeric value of the date you want, and assign that to the variable. But that involves even more calculations and steps. If you absolutely have to assume that your dates could be in a non-standard format, you have to build them piece by piece or mathematically.
On the other hand, I'll go with typing them in ISO format, and check that the server gets them correctly, and leave it at that. Much, much, much simpler, and more than reliable enough.
- 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
January 8, 2009 at 3:35 pm
I'm with you Gus. The ISO 8601 standard was godsend when it came out. I really would not want to have to go back to constructing dates by parts to be sure that they were correct.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 3:39 pm
Didn't someone write an article that talked about how DATEDIFF and DATEADD worked? I can't find one on SSC, so it may have been an article on a different site referenced from SSC at one time.
January 8, 2009 at 3:44 pm
stricknyn (1/8/2009)
Hello everyone,Ok this is weird. When I run the statement below it is returning 1. Can someone explain to me why this is returning 1?
select datediff(year, '2008-01-09 00:00', '2009-01-08 00:00')
This should return 0, but it's returning 1 and I can't figure out why.
Thanks,
Strick
For the same reason that select datediff(year,'2008-12-31 23:59:59.997','2009-01-01 00:00:00.000) returns 1.
That is the number of year boundaries crossed betweenthe two dates.
January 8, 2009 at 3:53 pm
RBarryYoung (1/8/2009)
Yeah, but "YDM" is not the date standard for any nation. That's why ISO choose it. And what is the alternative way to do this that is not either ambiguous or invalid under some possible dateformat/nationality setting?
The only unambiguous datetime format in SQL Server is YYYYMMDD (20081231). SQL Server will interpret it correctly no matter what the setting of DATEFORMAT is.
As Chris Morris already demonstrated in his post, YYYY-MM-DD (2008-12-31) is not unambiguous in SQL Server because it depends on the DATEFORMAT setting and can be interpreted as YYYY-DD-MM if the DATEFORMAT setting is YDM. YDM is not the default for any language, but it can be changed to that.
You can see what happens with the script below:
set dateformat dmy
go
select DT_1 =convert(datetime,'20081231')
go
select DT_2 = convert(datetime,'2008-12-31')
set dateformat ymd
go
select DT_3 =convert(datetime,'20081231')
go
select DT_4 = convert(datetime,'2008-12-31')
go
set dateformat ydm
go
select DT_5 =convert(datetime,'20081231')
go
select DT_6 =convert(datetime,'2008-12-31')
Results:
DT_1
------------------------------------------------------
2008-12-31 00:00:00.000
(1 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
DT_3
------------------------------------------------------
2008-12-31 00:00:00.000
(1 row(s) affected)
DT_4
------------------------------------------------------
2008-12-31 00:00:00.000
(1 row(s) affected)
DT_5
------------------------------------------------------
2008-12-31 00:00:00.000
(1 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Edit:
I forgot about the YYYY-MM-DDTHH:MM:SS.MIL format that RBarryYoung mentioned in the following post.
Make that TWO unambiguous datetime formats. :blush:
January 8, 2009 at 4:03 pm
I just checedk BOL again and according to it, the ISO 8601 format is unambiguous in SQL Server. Thing is, apparently I have been using a shortened colloquial form that does not have this benefit:
The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings.
Following are two examples of datetime values that are specified in the ISO 8601 format:
2004-05-23T14:25:102004-05-23T14:25:10.487
So, I guess I need to keep the "T"+time part as well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply