Lea Verou Lea Verou - 4 months ago 13
SQL Question

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?

DJ. DJ.
Answer

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 '2016-06-01' and dateadd(millisecond, -3, '2016-07-01')

i.e.

where myDateTime between '2016-06-01 00:00:00.000' and '2016-07-01 23:59:59.997'