MS SQL Server是否在“之间"包括范围边界? [英] Does MS SQL Server's "between" include the range boundaries?

问题描述

例如可以

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

选择5和10还是将它们排除在范围之外?

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

推荐答案

BETWEEN运算符包含在内.

The BETWEEN operator is inclusive.

从联机丛书中:

如果

BETWEEN的值返回TRUE， test_expression大于或 等于begin_expression的值 且小于或等于 end_expression.

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警告

注意:使用DateTimes时必须要小心；如果仅给出日期，则该值是从当天的午夜开始计算的；为避免在结束日期之内错过时间，或避免在多个范围的午夜重复捕获第二天的数据，结束日期应为迄今为止的第二天午夜之前的3毫秒. 3毫秒，因为任何小于此值的值都将四舍五入到第二天的午夜.

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.

例如要获得2016年6月内的所有值，您需要运行:

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

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

即

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

从日期减去3毫秒将使您容易受到3毫秒窗口中缺少行的影响.正确的解决方案也是最简单的解决方案:

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'

这篇关于MS SQL Server是否在“之间"包括范围边界?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！