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'