Coder Perfect

Does “between” in MS SQL Server include range boundaries?


For instance can

FROM bar

5 and 10 must be chosen, else they will be eliminated from the range?

Asked by Lea Verou

Solution #1

The operator BETWEEN is inclusive.

From Books Online:

DateTime Caveat

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.

Solution #2

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

Solution #3

SQL Server 2008 provides a real-world example.

Source data:

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


    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

Solution #4

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

Solution #5

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Answered by Russ Cam

Post is based on