Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use of DATETIME and BETWEEN - Part 2


Use of DATETIME and BETWEEN - Part 2

Author
Message
Dwayne Dibley
Dwayne Dibley
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 3166
Comments posted to this topic are about the item Use of DATETIME and BETWEEN - Part 2
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6135 Visits: 25280
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

Before posting a performance problem please read
weisietan
weisietan
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 26
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 ...
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6291 Visits: 7190
This was a good question.
However, remember that while the datetime2 datatype refernced in the answer will only return 3 rows, this was only added with SQL2008 and later versions (along with the DATE, TIME and DATETIMEOFFSET date types)

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18531 Visits: 13248
Nice question. This one should have been 2 points and the one of yesterday only 1 point.
Ah well :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18531 Visits: 13248
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


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6291 Visits: 7190
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 ...

Point of note:
You cannot use wildcards in a BETWEEN command, SQL Server takes them as verbose.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8744 Visits: 11718
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
cengland0
cengland0
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 1300
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
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18531 Visits: 13248
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)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search