MS SQL Server 的“之间”是否包括范围边界？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/749615/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does MS SQL Server's "between" include the range boundaries?
提问by Lea Verou
For instance can
例如可以
SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10
select 5 and 10 or they are excluded from the range?
选择 5 和 10 还是将它们排除在范围之外？
回答by DJ.
The BETWEEN operator is inclusive.
BETWEEN 运算符是包含性的。
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.
如果 test_expression 的值大于或等于 begin_expression 的值且小于或等于 end_expression 的值，则 BETWEEN 返回 TRUE。
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.
注意：使用 DateTimes 你必须小心；如果只给出了日期，则取值为当天午夜；为避免错过结束日期内的时间，或在多个范围内重复捕获第二天午夜的数据，您的结束日期应为截止日期后一天的午夜前 3 毫秒。3 毫秒，因为任何小于此值的值都将向上舍入到第二天的午夜。
e.g. to get all values within June 2016 you'd need to run:
例如，要获取 2016 年 6 月内的所有值，您需要运行：
where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')
where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')
i.e.
IE
where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'
where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'
datetime2 and datetimeoffset
datetime2 和 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:
从日期中减去 3 毫秒将使您容易受到 3 毫秒窗口中丢失的行的影响。正确的解决方案也是最简单的一种：
where myDateTime >= '20160601' AND myDateTime < '20160701'
回答by BradC
Yes, but be careful when using between for dates.
是的，但在日期之间使用时要小心。
BETWEEN '20090101' AND '20090131'
is really interpreted as 12am, or
真正解释为 12am，或
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:
所以会错过 1 月 31 日当天发生的任何事情。在这种情况下，您将不得不使用：
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!!
更新：完全有可能在一天的最后一秒内创建记录，日期时间最晚20090101 23:59:59.997！！
For this reason, the BETWEEN (firstday) AND (lastday 23:59:59)approach is not recommended.
出于这个原因，BETWEEN (firstday) AND (lastday 23:59:59)不推荐这种方法。
Use the myDate >= (firstday) AND myDate < (Lastday+1)approach instead.
请改用该myDate >= (firstday) AND myDate < (Lastday+1)方法。
回答by Ryan Rodemoyer
Real world example from SQL Server 2008.
来自 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
回答by Shaun
if you hit this, and don't really want to try and handle adding a day in code, then let the DB do it..
如果你点击了这个，并且不想尝试在代码中添加一天，那么让 DB 来做..
myDate >= '20090101 00:00:00' AND myDate < DATEADD(day,1,'20090101 00:00:00')
If you do include the time portion: make sure it references midnight. Otherwise you can simply omit the time:
如果您确实包含时间部分：请确保它引用午夜。否则，您可以简单地省略时间：
myDate >= '20090101' AND myDate < DATEADD(day,1,'20090101')
and not worry about it.
不用担心。
回答by Russ Cam
BETWEEN(Transact-SQL)
Specifies a(n) (inclusive) range to test.
之间(Transact-SQL)
指定要测试的 ( n)（含）范围。
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Arguments
test_expressionIs the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.
NOTSpecifies that the result of the predicate be negated.
begin_expressionIs any valid expression. begin_expression must be the same data type as both test_expression and end_expression.
end_expressionIs any valid expression. end_expression must be the same data type as both test_expression and begin_expression.
ANDActs as a placeholder that indicates test_expression should be within the range indicated by begin_expression and end_expression.
Remarks
To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.
Result Value
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.
NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.
参数
test_expression是在由 begin_expression 和 end_expression 定义的范围内测试的表达式。test_expression 必须与 begin_expression 和 end_expression 的数据类型相同。
NOT指定谓词的结果是否定的。
begin_expression是任何有效的表达式。begin_expression 必须与 test_expression 和 end_expression 的数据类型相同。
end_expression是任何有效的表达式。end_expression 必须与 test_expression 和 begin_expression 的数据类型相同。
AND充当占位符，指示 test_expression 应在 begin_expression 和 end_expression 指示的范围内。
评论
要指定独占范围，请使用大于 (>) 和小于运算符 (<)。如果 BETWEEN 或 NOT BETWEEN 谓词的任何输入为 NULL，则结果为 UNKNOWN。
结果值
如果 test_expression 的值大于或等于 begin_expression 的值且小于或等于 end_expression 的值，则 BETWEEN 返回 TRUE。
如果 test_expression 的值小于 begin_expression 的值或大于 end_expression 的值，则 NOT BETWEEN 返回 TRUE。
回答by Kahlil Vanz
If the column data type is datetime then you can do this following to eliminate time from datetime and compare between date range only.
如果列数据类型是日期时间，那么您可以执行以下操作以从日期时间中消除时间并仅在日期范围之间进行比较。
where cast(getdate() as date) between cast(loginTime as date) and cast(logoutTime as date)
回答by Halim
It does includes boundaries.
它确实包括边界。
declare @startDate date = cast('15-NOV-2016' as date)
declare @endDate date = cast('30-NOV-2016' as date)
create table #test (c1 date)
insert into #test values(cast('15-NOV-2016' as date))
insert into #test values(cast('20-NOV-2016' as date))
insert into #test values(cast('30-NOV-2016' as date))
select * from #test where c1 between @startDate and @endDate
drop table #test
RESULT c1
2016-11-15
2016-11-20
2016-11-30
declare @r1 int = 10
declare @r2 int = 15
create table #test1 (c1 int)
insert into #test1 values(10)
insert into #test1 values(15)
insert into #test1 values(11)
select * from #test1 where c1 between @r1 and @r2
drop table #test1
RESULT c1
10
11
15
回答by user2296528
I've always used this:
我一直用这个：
WHERE myDate BETWEEN startDate AND (endDate+1)
WHERE myDate BETWEEN startDate AND (endDate+1)
