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
November 18, 2011 at 12:48 am

This was removed by the editor as SPAM
November 18, 2011 at 1:15 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 37 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: 259115 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: 259115 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: 259115 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: 6492 More actions · Answer 8

Michael Lysons

SSCertifiable

Points: 6492

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: 259115 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