For instance can
SELECT foo FROM bar WHERE foo BETWEEN 5 AND 10
5 and 10 must be chosen, else they will be eliminated from the range?
Asked by Lea Verou
The operator BETWEEN is inclusive.
From Books Online:
NB: When using DateTimes, be aware that if only a date is specified, the value is calculated as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day’s data at midnight in multiple ranges, set your end date to 3 milliseconds before midnight on the day following your to date. If the value is less than 3 milliseconds, the value will be rounded up to midnight the next day.
To get all values inside June 2016, for example, run:
where myDateTime is between ‘20160601’ and DATEADD(millisecond, -3, ‘20160701’) and myDateTime is between ‘20160601’ and DATEADD(millisecond, -3, ‘20160701’)
where myDateTime between ‘20160601 00:00:00.000’ and ‘20160630 23:59:59.997’
When you subtract 3 milliseconds from a date, you risk missing rows within the 3 millisecond window. The correct answer is also the most straightforward:
where myDateTime >= '20160601' AND myDateTime < '20160701'
Answered by DJ.
Yes, however when using between for dates, be cautious.
BETWEEN '20090101' AND '20090131'
is translated as 12 a.m., or
BETWEEN '20090101 00:00:00' AND '20090131 00:00:00'
As a result, anything that happened on January 31st will be missed. In this instance, you’ll need to utilize the following:
myDate >= '20090101 00:00:00' AND myDate < '20090201 00:00:00' --CORRECT!
BETWEEN '20090101 00:00:00' AND '20090131 23:59:59' --WRONG! (see update!)
UPDATE: Records can be made right up until the final second of the day, with a datetime as late as 20090101 23:59:59.997!!
As a result, the method BETWEEN (firstday) AND (lastday 23:59:59) is not suggested.
Instead, use the myDate >= (firstday) AND myDate (Lastday+1) method.
This is a good article on the subject.
Answered by BradC
SQL Server 2008 provides a real-world example.
ID Start 1 2010-04-30 00:00:01.000 2 2010-04-02 00:00:00.000 3 2010-05-01 00:00:00.000 4 2010-07-31 00:00:00.000
SELECT * FROM tbl WHERE Start BETWEEN '2010-04-01 00:00:00' AND '2010-05-01 00:00:00'
ID Start 1 2010-04-30 00:00:01.000 2 2010-04-02 00:00:00.000
Answered by Ryan Rodemoyer
If you run into this problem and don’t want to deal with adding a day in code, let the database do it.
myDate >= '20090101 00:00:00' AND myDate < DATEADD(day,1,'20090101 00:00:00')
If you do include the time element, be sure it refers to 12 a.m. Otherwise, simply leave out the time:
myDate >= '20090101' AND myDate < DATEADD(day,1,'20090101')
and not be concerned about it
Answered by Shaun
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Answered by Russ Cam
Post is based on https://stackoverflow.com/questions/749615/does-ms-sql-servers-between-include-the-range-boundaries