November 18, 2011 at 6:20 am
Koen Verbeeck (11/18/2011)
.001 --> .000.002 --> .003
.999 --> .000 (this has the possibility to "jump" to the next day)
-- This little snippet shows that that is correct. Answer comes back as: 2011-11-19 00:00:00.000
CREATE TABLE #Test(Sample DATETIME)
INSERT INTO #Test VALUES('2011-11-18 023:59:59:999')
SELECT Sample FROM #Test
DROP TABLE #Test
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
November 18, 2011 at 7:47 am
Hi all
Thanks for the feedback on the question. The results as they stand make for some interesting reading. Only 31% got the answer correct. So this shows that the loss of precision is not as well understood as it could be.
Out of the incorrect answers, those giving either of the two final options (21% of total) demonstrate an awareness of the rounding issue but not which direction the rounding occurred.
I'd be interested in the reasoning as to why people chose the first answer. Perhaps it was an unfamiliarity with the syntax or they thought it could have been a trick question.
That leaves 45% of the sample who were unaware of both.
For some applications, this may not be a problem. For those where a high precision fot datetime recording is required, then it is something to look out for.
November 18, 2011 at 8:18 am
Good question, thanks.
November 18, 2011 at 9:04 am
Good question - surprisingly not widely known in some of the places I've worked. ...until it bit them square in the app! 😛 😀
Cheers
November 18, 2011 at 9:33 am
Very good question. Obvious, but worth noting, I think, that the rounding occurs on the assignment to a datetime variable or column, NOT when doing the SELECT. In other words, after running your sample script, the values you'd see on a flat select (without the WHERE) would NOT be the values you entered, but would already be rounded up or down.
Rob Schripsema
Propack, Inc.
November 18, 2011 at 10:35 am
Good question, Thanks!
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
November 18, 2011 at 10:37 am
Great question. Thanks!
November 18, 2011 at 11:55 am
Good question.
Koen Verbeeck (11/18/2011)
cengland0 (11/18/2011)
Okay, so I knew it rounds the milliseconds to .000, .003, or .007; however, I didn't know when it rounds up or down.So, when the time ends in .001, what does it round to?
same question for .002 and .999 because those are the ones in the QOTD that messed me up.
.001 --> .000
.002 --> .003
.999 --> .000 (this has the possibility to "jump" to the next day)
for 1,2,4,6,8,9 it's round nearest - the natural rounding method, that you can see in the string
9->0<-1 2->3<-4 5 6->7<-8
But (as the formatting makes obvious I hope) that doesn't tell us which way 5 rounds.
I think 005 always rounds .005 ms to to .007 ms. I'm not sure I like that behaviour, but I think that's what it does.
Tom
November 18, 2011 at 3:48 pm
Sweet - good question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2011 at 5:15 pm
Good QOD. This rounding quirkiness is why I've made a habit of avoiding BETWEEN with datetime types. If you have to use BETWEEN, then be aware that the inclusive end-point for a date must end with .997, or .998 if you really want to be crazy, but not .999. That's so completely counter-intuitive that you'd better have a good reason to use that syntax. Rather, I'd suggest you code to specify the start and end of a range with separate conditions.
declare @startDate datetime
declare @endDate datetime
set @startDate = '2011-09-01'
set @endDate = '2011-10-31'
Select ID, tranTime from SomeTable
where TranTime >= @startDate
and TranTime < @endDate + 1
-----edit: compare to @startdate with >=, not just > (Did I miss that, or did the SSC formatter mess with it? Dunno, but I had to correct.
November 18, 2011 at 5:20 pm
L' Eomot Inversé (11/18/2011)
I think 005 always rounds .005 ms to to .007 ms. I'm not sure I like that behaviour, but I think that's what it does.
I think you're right. Just like, when rounding numerical data, .5 always rounds up.
November 18, 2011 at 10:29 pm
Man... that was not an easy one. Took me almost half an hour to figure it out. THANKS!
November 19, 2011 at 12:27 pm
Revenant (11/18/2011)
Man... that was not an easy one. Took me almost half an hour to figure it out. THANKS!
I'm glad that you got so much out of it. Thank you. I enjoy answering QOTDs, and feedback such as yours encourages me to submit more
🙂
November 19, 2011 at 4:42 pm
Nice question.
November 21, 2011 at 9:19 am
good question!!!
thanks!
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply