March 4, 2009 at 8:29 am
David: we are going off topic.
Quite :ermm:
I think we all agree that again we have a QoD which is not locale neutral.
Yep 😉
My post was not meant to cause offence or consternation in any way :blush:
Far away is close at hand in the images of elsewhere.
Anon.
March 4, 2009 at 8:35 am
Ninja's_RGR'us (3/4/2009)
...
I'm going to go with:
b) 2009-02-09 16:35:56.789
UPDATE: I ran the result and got c) 2009-02-09 16:33:56.790. (My original second choice.) Why do the milliseconds get rounded up?
Thanks,
webrunner
It's the limit of the precision in ms. If the last digit is not a possible value, it gets rounded up or down depending on the closest number.
Thanks, but could you explain in more detail what happened? I still don't quite understand how the rounding works in this case.
For example, I ran this:
select DATEADD(MINUTE,-59,'2009-02-09 12:34:56.785')
and got this:
2009-02-09 11:35:56.787
I know SQL is doing something to get that result, but if anything I thought it would end in .786 in this case, yet it ends in .787.
Thanks again!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 4, 2009 at 9:56 am
webrunner,
.786 isn't a valid millisecond for the datetime datatype...
All datetimes end in either .xx0, .xx3 or .xx7
.xx9, .xx0 and .xx1 are rounded up/down to .xx0
.xx2, .xx3, .xx4 are rounded up/down to .xx3
.xx5, .xx6, .xx8 are rounded up/down to .xx7
Check out the BOL "Date and Time" for an example table.
Thanks,
Chad
March 4, 2009 at 10:13 am
Chad Crawford (3/4/2009)
webrunner,.786 isn't a valid millisecond for the datetime datatype...
All datetimes end in either .xx0, .xx3 or .xx7
.xx9, .xx0 and .xx1 are rounded up/down to .xx0
.xx2, .xx3, .xx4 are rounded up/down to .xx3
.xx5, .xx6, .xx8 are rounded up/down to .xx7
Check out the BOL "Date and Time" for an example table.
Thanks,
Chad
Wow, that is odd (well, except for .xx0 :)), but thank you so much for telling me. I never would have figured it out, and there it is spelled out in BOL.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 4, 2009 at 11:11 am
I thought it was a dead giveaway that only one answer had the correct date prefix (2009-02-09). The wrong answers had 2009-02-10 and 2009-02-08. Except for clicking on the wrong button on mistake, I can't understand why anyone would miss this question.
March 4, 2009 at 11:43 am
jpowers (3/4/2009)
I thought it was a dead giveaway that only one answer had the correct date prefix (2009-02-09). The wrong answers had 2009-02-10 and 2009-02-08. Except for clicking on the wrong button on mistake, I can't understand why anyone would miss this question.
I got the original question correct, not because of the date but simply by doing the calculations in my head (in this case that was relatively easy for me). I almost second-guessed myself because it seemed uncharacteristic that the obvious QOTD answer was the correct one. Usually there is some trick involved.
But I got the second question (the one posted in the comments) wrong, because of the way SQL Server rounds milliseconds. Go figure, as they say....
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 4, 2009 at 11:50 am
Webrunner (and others following the subthread on my supplemental quiz question),
The rounding of milliseconds in DATETIME data is to .000, .003, or .007. Keep this in mind when designing queries that test for a time being within a range. An easy error to make would be to try to define a day with the BETWEEN operator. If we want to see if a time value is on the same day as some other time, you may be tempted to strip that target datetime down to just the date and then append a "highest possible" time value.
With this code, a Time_1 value of '2009-03-05 00:00:00.001' would return true, probably not what you'd want.
declare @time_1 datetime
declare @startTime datetime
declare @endTime datetime
set @time_1 = '2009-03-05 00:00:00.001'
set @startTime = '2009-03-04 00:00:00.00'
set @endTime = '2009-03-04 23:59:59.999'
Print '@startTime is ' + convert(char(23),@startTime,121)
Print '@time_1 is ' + convert(char(23),@time_1,121)
Print '@endTime is ' + convert(char(23),@endTime,121)
If @time_1 between @startTime and @endTime
print 'True'
Play with the values of @time_1 to understand the effect of rounding.
A better date-range test would be to test for greater than or equal to the start of a date and less than the start of the next date.
declare @time_1 datetime
set @time_1 = '2009-03-05 00:00:00.001'
If @time_1 >= '2009-03-04' and @time_1 < '2009-03-05' -- time portion defaults to zeroes
print 'True'
else
print 'False'
One final note: SQL 2008 introduces some new data types that allow separation of date and time and also greater precision on time.
March 5, 2009 at 5:52 am
That was too easy question.
March 10, 2009 at 1:31 pm
I thought this was a trick question, but got it right anyway, whew~
November 30, 2009 at 9:33 pm
answer c
February 12, 2013 at 10:42 pm
easy one +1:-)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply