Does MS SQL Server's "between" include the range boundaries?

For instance can

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

select 5 and 10 or they are excluded from the range?

Solution 1:

The BETWEEN operator is inclusive.

From Books Online:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

DateTime Caveat

NB: With DateTimes you have to be careful; if only a date is given the value is taken 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, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.

e.g. to get all values within June 2016 you'd need to run:

where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')

i.e.

where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'

datetime2 and datetimeoffset

Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:

where myDateTime >= '20160601' AND myDateTime < '20160701'

Solution 2:

Yes, but be careful when using between for dates.

BETWEEN '20090101' AND '20090131'

is really interpreted as 12am, or

BETWEEN '20090101 00:00:00' AND '20090131 00:00:00'

so will miss anything that occurred during the day of Jan 31st. In this case, you will have to use:

myDate >= '20090101 00:00:00' AND myDate < '20090201 00:00:00'  --CORRECT!

or

BETWEEN '20090101 00:00:00' AND '20090131 23:59:59' --WRONG! (see update!)

UPDATE: It is entirely possible to have records created within that last second of the day, with a datetime as late as 20090101 23:59:59.997!!

For this reason, the BETWEEN (firstday) AND (lastday 23:59:59) approach is not recommended.

Use the myDate >= (firstday) AND myDate < (Lastday+1) approach instead.

Good article on this issue here.

Solution 3:

Real world example from SQL Server 2008.

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

Query:

SELECT
    *
FROM
    tbl
WHERE
    Start BETWEEN '2010-04-01 00:00:00' AND '2010-05-01 00:00:00'

Results:

ID    Start
1     2010-04-30 00:00:01.000
2     2010-04-02 00:00:00.000