MS SQL Server的“between”是否包含范围边界？

Question

例如，能否

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

选择5和10是否被排除在范围之外？

- Lea Verou

---

长话短说，它是两端都包含的。如果您想在一端包含而在另一端排除，或者两端都排除，请使用两个谓词和 < > >= <= 适当地。 - Caius Jard

Answer 1

BETWEEN运算符是包含边界的。

来自Books Online:

如果test_expression的值大于或等于begin_expression的值且小于或等于end_expression的值，则BETWEEN返回TRUE。

DateTime注意事项

注：使用DateTime时要小心；如果只提供日期，则该值将被视为该日午夜的时间；为了避免错过结束日期内的时间，或在多个范围中重复捕获以下一天午夜的数据，您的结束日期应该是您所需日期后面第二天午夜之前3毫秒。3毫秒是因为小于此值将导致值向上舍入到下一天的午夜。

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

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

即：

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

datetime2和datetimeoffset

从日期减去3毫秒会使您容易错过3毫秒窗口中的行。正确的解决方案也是最简单的解决方案：

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

- DJ.

---

使用BETWEEN操作符来筛选两个日期间的日期时间时，你也可以将日期时间转换为日期格式，例如：where CONVERT(DATE, MyDate) BETWEEN '2017-09-01' and '2017-09-30'。这种方法使得日期时间中的时间部分变得无关紧要。 - Pete

不要试图从日期中减去3毫秒，否则会错过那3毫秒内的内容。同时也不要将 datetime 转换为 date，因为这会使索引失效。请使用标准的 WHERE OrderDate >= '20160601' AND OrderDate < '20160701'。此外，请确保使用 yyyymmdd 格式，而不是 yyyy-mm-dd 格式，因为后者与服务器的 mdy、dmy、ymd、ydm、myd 和 dym 设置相关，可能会被误解。 - Ian Boyd

@IanBoyd 我以为 yyyy-mm-dd 是通用的日期格式，没有文化使用 yyyy-dd-mm。我认为有人使用 yyyy.dd.mm，但即使您将文化设置为该格式，它仍会正确解释 yyyy-mm-dd。您有可以测试的示例文化吗？ - Robert McKee

dz-BT 使用 yyyy-mm-dd。en-CA、fr-CA、xh-ZA、rw-RW、ko-KR、lt-LT、smj-SE、se-NO、se-SE、sma-SE、st-ZA、nso-ZA、tn-BW、tn-ZA、si-LK、sv-SE、ug-CN、ve-ZA、ts-ZA 也是如此。 - Ian Boyd

Answer 2

可以使用 "between" 来处理日期，但需要小心。

BETWEEN '20090101' AND '20090131'

被解释为12am

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

如果你使用这种方法，你会错过1月31日当天发生的所有事件。在这种情况下，你需要使用：

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

或者

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

更新: 在一天的最后一秒钟甚至可以创建具有晚至20090101 23:59:59.997的日期时间的记录！

因此，不建议使用BETWEEN (firstday) AND (lastday 23:59:59)的方式。

请改用myDate >= (firstday) AND myDate < (Lastday+1)的方式。

这里有一篇关于此问题的好文章。

- BradC

---

字符串也存在类似的问题，例如 WHERE col BETWEEN 'a' AND 'z' 将会排除大部分以 z 开头的行。 - Martin Smith

当涉及到日期时间的处理时，这一点是正确的；但如果你正在处理日期时间，那就不应该感到惊讶。这类似于指出BETWEEN 5 AND 10不包括10.2的情况... - Andrzej Doyle

将datetime转换为DATE类型可以起作用：CAST(DATE_TIME_COL AS DATE) BETWEEN '01/01/2009' AND '01/31/2009'。 - craig

@craig，没错，只要你使用的是SQL 2008或更高版本，那么日期数据类型就被引入了。此外，该语法将为每一行转换该值，因此无法在该字段上使用任何索引（如果这是一个问题的话）。 - BradC

完全有可能在当天的最后一秒钟创建记录，日期时间可以晚至01/01/2009 23:59:59.997。你难道不能使用 AND '01/31/2009 23:59:59.99999999' 或者需要多少个9来实现吗？ - wal

Answer 3

来自SQL Server 2008的真实世界示例。

源数据：

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

查询：

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

结果：

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

- Ryan Rodemoyer

---

说实话，我没看到你的回答。也许是我的网络提供商把你的截图隐藏了（如果你发了的话）。 - anar khalilov

为什么ID为3的那一行被排除了？因为它的“Start”值与“BETWEEN”的上限值相等，而“BETWEEN”是一个包含范围，不包括上限值。 - Dai

更好的答案带有结果。 - Samir

Answer 4

如果你遇到这个问题，而且不想在代码中尝试处理添加一天的操作，那就让数据库来处理吧。

myDate >= '20090101 00:00:00' AND myDate < DATEADD(day,1,'20090101 00:00:00')

如果您包括时间部分，请确保它参考午夜。否则，您可以简单地省略时间：

myDate >= '20090101' AND myDate < DATEADD(day,1,'20090101')

而不用担心它。

- Shaun

Answer 5

BETWEEN (Transact-SQL)

指定一个（包含）范围进行测试。

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Arguments

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。NOT BETWEEN返回TRUE，如果test_expression的值小于begin_expression的值或大于end_expression的值。

- Russ Cam

Answer 6

如果列数据类型是datetime，您可以按照以下方式消除datetime中的时间，并仅在日期范围内进行比较。

where cast(getdate() as date) between cast(loginTime as date) and cast(logoutTime as date)

- Kahlil Vanz

---

这比在结束日期上添加+1更有效。我同意Andrew Morton的观点——如果它不是可搜索的，那么改变列数据类型或添加一个仅包含预先计算日期的第二个列可能会提高性能。 - Arno Peters

Answer 7

它确实包含边界。

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

- Halim

Answer 8

我一直使用这个：

WHERE myDate BETWEEN startDate AND (endDate+1)

- Bakchod Guru