Use of DATETIME and BETWEEN - Part 2

Question

Post reply

Use of DATETIME and BETWEEN - Part 2

Dwayne Dibley

SSCrazy

Points: 2350
More actions
November 17, 2011 at 9:21 pm

#250772

Comments posted to this topic are about the item Use of DATETIME and BETWEEN - Part 2

Viewing 15 posts - 1 through 15 (of 36 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Thanks for a good question on fundamentals of the DATETIME data type.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

weisietan Grasshopper Points: 23 More actions · Answer 2

I try using the sql server 2000 to retrieve date using Between

the first data will counted but the last will not counted.

Example:

Name between 'A%' and 'C%'

just will get the A & B data only..

so ...the answer i think no so correct ...

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

Nice question. This one should have been 2 points and the one of yesterday only 1 point.

Ah well

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 4

weisietan (11/18/2011)
I try using the sql server 2000 to retrieve date using Between
the first data will counted but the last will not counted.
Example:
Name between 'A%' and 'C%'
just will get the A & B data only..
so ...the answer i think no so correct ...

According to MSDN, the behaviour of BETWEEN and DATETIME is exactly the same as in later versions, meaning that the boundaries of the BETWEEN are included in the result set.

Your example also uses a string datatype, and not dates.

http://msdn.microsoft.com/en-us/library/aa258277(v=SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa225976(v=SQL.80).aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

weisietan (11/18/2011)
I try using the sql server 2000 to retrieve date using Between
the first data will counted but the last will not counted.
Example:
Name between 'A%' and 'C%'
just will get the A & B data only..
so ...the answer i think no so correct ...

Your BETWEEN clause translates to:

WHERE Name >= 'A%' AND NAME <= 'C%'

This means that a name that is just 'A' will not match (it sorts before 'A%' in any collation I know). A name that is just 'C' will match, as will any name that starts with a C and has a second character that sorts before the % sign in your collation, optionally followed by more characters. Here's a repro:

CREATE TABLE Test

(Name varchar(20) COLLATE Latin1_General_CI_AI);

go

INSERT INTO Test (Name) VALUES ('A')

INSERT INTO Test (Name) VALUES ('B')

INSERT INTO Test (Name) VALUES ('C')

INSERT INTO Test (Name) VALUES ('A$')

INSERT INTO Test (Name) VALUES ('B$')

INSERT INTO Test (Name) VALUES ('C$')

INSERT INTO Test (Name) VALUES ('A%')

INSERT INTO Test (Name) VALUES ('B%')

INSERT INTO Test (Name) VALUES ('C%')

INSERT INTO Test (Name) VALUES ('Aa')

INSERT INTO Test (Name) VALUES ('Bb')

INSERT INTO Test (Name) VALUES ('Cc')

INSERT INTO Test (Name) VALUES ('A$Z')

INSERT INTO Test (Name) VALUES ('B$Z')

INSERT INTO Test (Name) VALUES ('C$Z')

SELECT Name FROM Test

WHERE Name BETWEEN 'A%' AND 'C%'

ORDER BY Name;

go

DROP TABLE Test;

The % sign has a special meaning is LIKE searches only.

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/

cengland0 SSCertifiable Points: 6102 More actions · Answer 6

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.

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 7

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)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Michael Lysons SSCertifiable Points: 6496 More actions · Answer 8

Michael Lysons

SSCertifiable

Points: 6496

November 18, 2011 at 4:54 am

#1409803

Great question - thank you.

SkyBVI SSCoach Points: 15128 More actions · Answer 9

Oops...I thot correct answer should be 3,4,5

Didn't understand why it will round off when datatype is datetime.??

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 10

SKYBVI (11/18/2011)
Oops...I thot correct answer should be 3,4,5
Didn't understand why it will round off when datatype is datetime.??
Regards,
Skybvi

If you read the MSDN article referenced in the explanation, you will notice that the datetime datatype is only accurate for every 3 milliseconds. In other words, time goes like this for datetime:

2011-11-18T14:06:46.000

2011-11-18T14:06:46.003

2011-11-18T14:06:46.007

2011-11-18T14:06:46.010

...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Thomas Abraham SSChampion Points: 10761 More actions · Answer 11

Thanks Dwayne for an excellent question!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

Good question, got it wrong, learned something today about time rounding. Well, re-learned.....:ermm:

Rich

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

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 14

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.

Use of DATETIME and BETWEEN - Part 2

Cookies on SQLServerCentral