February 17, 2009 at 6:16 am
Wow! As of 8AM EST, only one person got this question right. As far as I can tell, out of all the answers only two dates are excluded, 2009-01-30 00:00:00.000 and 2009-02-01 00:00:00.003. Which mean multiple answers apply. Did anyone else get the same result?
Greg
February 17, 2009 at 6:18 am
Sam Iam (2/17/2009)
I didn't know that SQL Server rounded datetime...this is confusing behavior because I wouldn't have expected to see the '20090201' date in the result set. Try explaining that one to your business users.
Hi Sam,
You don't have to explain this if you always remember the best practice: when filtering on time intervals, avoid BETWEEN. Use >= for the start of the interval, and < (not <= !!) for the end.
That doesn't only prevent this problem, it also saves a lot of headaches and changes if you ever switch to a different datatype (e.g. smalldatetime, or datetime2(7) if you're on SQL Server 2008, etc.)
February 17, 2009 at 6:22 am
Do the QotDs get moderated/checked at all? This would have prevented much wailing and gnashing of teeth.
February 17, 2009 at 6:27 am
Hugo Kornelis (2/17/2009)
Hi Glen,
No, that's not correct. The upper limit in the BETWEEN proposition (2009-01-31 23:59:59.999) has to be converted to a proper datetime value. It can either be rounded down (to 2009-01-31 23:59:99.997), or up (to 2009-02-01 0:00:00.000). The rounding will go to the nearest value that can be represented, so in this case the rounding will be up. After the conversion, the query will work as if it was stated as "... BETWEEN ... AND 2009-02-01 0:00:00.000, and hence the latter datetime value will be returned as well.
Does this depend on the Collation Set being used and/or the regional settings on the server/PC because I've just run the OPs code and I don't get 2009-02-01 returned at all?
February 17, 2009 at 6:32 am
But you still have to remember that inserted/updated values will also be rounded. So if you try to insert '2009-01-31 23:59:59.999' it will be rounded to the next day. If you expect the data to end upp in '2009-01-31' you will get the wrong result. But probably it is no big issue if the date is rounded.
/Håkan
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 17, 2009 at 6:36 am
Lempster (2/17/2009)
Hugo Kornelis (2/17/2009)
Hi Glen,
No, that's not correct. The upper limit in the BETWEEN proposition (2009-01-31 23:59:59.999) has to be converted to a proper datetime value. It can either be rounded down (to 2009-01-31 23:59:99.997), or up (to 2009-02-01 0:00:00.000). The rounding will go to the nearest value that can be represented, so in this case the rounding will be up. After the conversion, the query will work as if it was stated as "... BETWEEN ... AND 2009-02-01 0:00:00.000, and hence the latter datetime value will be returned as well.
Does this depend on the Collation Set being used and/or the regional settings on the server/PC because I've just run the OPs code and I don't get 2009-02-01 returned at all?
I guess regional settings may affect your result. How do you write datetime in your region? Do you get '2009-01-31'?
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 17, 2009 at 6:46 am
hakan.winther (2/17/2009)
But you still have to remember that inserted/updated values will also be rounded. So if you try to insert '2009-01-31 23:59:59.999' it will be rounded to the next day. If you expect the data to end upp in '2009-01-31' you will get the wrong result. But probably it is no big issue if the date is rounded./Håkan
Just try executing the T-SQL code given in the explanantion and see what you get.
February 17, 2009 at 6:46 am
hakan.winther (2/17/2009)
I guess regional settings may affect your result. How do you write datetime in your region? Do you get '2009-01-31'?
Hi Håkan,
Regional settings may affect the results, but not the way datetime values are rounded. The only possible result of datetime interpretation mismatch would be a conversion error, if SQL Server decides that 01 is the day number, and 31 the month.
That being said, I don't think that there are currently any locale settings where yyyy-mm-dd hh:mm:ss.mmm is misunderstood. However, the only formats I know for 100% sure to be safe are:
* yyyy-mm-ddThh:mm:ss.mmm (note the uppercase T instead of a space between the date and the time part. Also note that the milliseconds (.mmm) are optional).
* yyyymmdd (weird, isn't it? Leave out the time, and you suddenly have to leave out the dashes as well. I seem to recall that there are one or two locale settings where yyyy-mm-dd, with dashes, can cause problems. O well, nobody ever promised me that this would be easy, or logical :D)
February 17, 2009 at 6:50 am
I would write it as '2009-01-31' and all my returned values are for that date. I'm using the Collation Latin1_General_CI_AS, my Location is set to United Kingdom and my 'Standard and formats' setting on my PC is set to English (United Kingdom).
Regards
Lempster
February 17, 2009 at 7:03 am
Lempster (2/17/2009)
I would write it as '2009-01-31' and all my returned values are for that date. I'm using the Collation Latin1_General_CI_AS, my Location is set to United Kingdom and my 'Standard and formats' setting on my PC is set to English (United Kingdom).Regards
Lempster
Hi Lempster,
You're right. I tried executing the script after changing the language settings of SQL Server to UK English by prepending a "SET LANGUAGE British;" statement, and I promptly got a conversion error.
To fix this, you should do either one of those:
a) Prepend a "SET LANGUAGE English': statement before the script to force US English settings; this will interpret the date/time formats used correctly, *OR*
b) Change all of the date/time constants in the INSERT ... UNION ALL statement to use the format yyyy-mm-ddThh:mm:ss.mmm. That is, change each space to an uppercase T and each semicolon before the milliseconds to a dot. Do not change the date/time format in the query, *OR* make these aforementioned changes AND add hyphens between the date parts.
With these changes, the code should run, showing how Feb 1st, midnight gets included in the results as well.
And for Steve (you are reading, right?) - after finding this, I can only say that the last of the answer possibilities, the conversion error, should be added to the list of correct answers.
February 17, 2009 at 7:09 am
Hi Hugo,
I don't get a conversion error, I simply get all of my results showing as '2009-01-31' plus whatever the hours, minutes and seconds are, so far from needing to 'fix' it, I regard this behaviour as more correct than the official 'correct' answer as it makes logical sense! As another poster pointed out, try explaining to business users why a date range returns dates outside of that range.
February 17, 2009 at 7:14 am
I agree, there is no way 01/30/2009 will be returned. After answering incorrectly, I remembered having the same thing happen to me a couple of years ago.
Roy
February 17, 2009 at 7:24 am
Lempster (2/17/2009)
Hi Hugo,I don't get a conversion error, I simply get all of my results showing as '2009-01-31' plus whatever the hours, minutes and seconds are, so far from needing to 'fix' it, I regard this behaviour as more correct than the official 'correct' answer as it makes logical sense! As another poster pointed out, try explaining to business users why a date range returns dates outside of that range.
Hi Lempster,
That is really weird. I don't see how that can happen.
Did you copy and paste the entire repro script given in the explanation? And did you make no modifications at all when running it?
What happens if you comment out or remove this line from the script:
AND AnswerLetter ='a' --change letter to verify the result set.
You should get two result sets - one including several (three) rows showing Feb 1st; the second (from the second query, the correct one) omitting those.
February 17, 2009 at 7:32 am
Lempster (2/17/2009)
Do the QotDs get moderated/checked at all? This would have prevented much wailing and gnashing of teeth.
In Steve's defense:
http://www.sqlservercentral.com/articles/SQLServerCentral.com/62764/
February 17, 2009 at 7:36 am
Hi Hugo,
Yes, you are right! My fault for not reading the script properly - the AND clause will obviously stop any rows with 2009-02-01 being returned. Doh!
Anyway, a salutory lesson in not using BETWEEN for date ranges...at least in SQL 2000/2005.
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply