Use of DATETIME and BETWEEN - Part 2

Question

Post reply

Use of DATETIME and BETWEEN - Part 2

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

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

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 2

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 3

Cliff Jones

SSChampion

Points: 10547

November 18, 2011 at 8:18 am

#1409947

Good question, thanks.

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 4

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

November 18, 2011 at 9:04 am

#1410000

Good question - surprisingly not widely known in some of the places I've worked. ...until it bit them square in the app! 😛 😀

Cheers

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

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.

Trey Staker SSCarpal Tunnel Points: 4766 More actions · Answer 6

Trey Staker

SSCarpal Tunnel

Points: 4766

November 18, 2011 at 10:35 am

#1410067

Good question, Thanks!

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

ChrmnMAO Say Hey Kid Points: 675 More actions · Answer 7

ChrmnMAO

Say Hey Kid

Points: 675

November 18, 2011 at 10:37 am

#1410069

Great question. Thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

SQLRNNR

SSC Guru

Points: 281324

November 18, 2011 at 3:48 pm

#1410235

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

john.arnott SSChampion Points: 11882 More actions · Answer 10

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.

Hugo Kornelis SSC Guru Points: 64748 More actions · Answer 11

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/