SQL Server：如何在两个日期之间检索记录？

Question

在SQL中，我编写了一个SELECT语句来获取两个日期之间的数据，使用BETWEEN AND。

示例：

select * 
from xxx 
where dates between '2012-10-26' and '2012-10-27'

但返回的行仅适用于26日，而不是26日和27日。

您能帮助我吗？谢谢。

- Ssasidhar

---

取决于您使用的 SQL Server 版本以及您的“日期”列是什么数据类型... - marc_s

另外，在进行此操作时，我建议使用一种安全的日期/时间格式——它适用于所有SQL Server版本，并且独立于任何日期、区域或语言设置。这可以是20121017 23:59:59.997（日期使用YYYYMMDD，无需使用破折号！）或2012-10-17T23:59:59.997（日期中使用破折号，并在日期和时间部分之间使用固定的**T**作为分隔符）。 - marc_s

我认为这个链接可以帮助解决你的问题：https://dev59.com/x3E95IYBdhLWcg3wGZ5_ - Campinho

Answer 1

如其他人所回答的，您可能拥有一个DATETIME（或其他变体）列，而不是DATE数据类型。

以下是适用于所有情况的条件，包括DATE：

SELECT * 
FROM xxx 
WHERE dates >= '20121026' 
  AND dates <  '20121028'    --- one day after 
                             --- it is converted to '2012-10-28 00:00:00.000'
 ;

@Aaron Bertrand在这篇文章中讲解了BETWEEN和魔鬼的共同点。

- ypercubeᵀᴹ

---

Aaron Bertrand的博客链接非常有帮助，因为它解释了当你在datetime列上使用BETWEEN查询时为什么会遇到问题。我很久以前就学会了总是使用between '1/1/2016 00:00:00'和'1/31/2016 23:59:59'而不是between '1/1/2016'和'1/31/2016'，但从来没有时间去探究为什么。 - James Toomey

@JamesToomey，是的，这篇博客文章非常出色。你知道如果列的类型允许毫秒级别，那么你的BETWEEN语句将会失败（我真心希望你在日期/时间字面量中没有真正使用“m/d/yyyy”格式！）。 - ypercubeᵀᴹ

@TypoCubeᵀᴹ，我可能即将暴露自己的无知，但我总是乐于学习新东西 - m/d/yyyy查询有什么危险？我喜欢使用yyyy/mm/dd进行屏幕输出，因为如果用户将其复制/粘贴到文本文件/其他地方，它会按字母顺序排序，但我怀疑你说的更多的是查询返回不准确数据的风险... - James Toomey

正确的，当涉及到输出时，格式并不重要，可以按照用户喜好的任何格式进行输出。但是对于输入（例如插入、查询/过程中的参数），就存在一些问题。我有一些解决模糊格式问题的答案，但我会引用Aaron的另一篇文章：改掉的坏习惯：处理日期/范围查询错误。 - ypercubeᵀᴹ

类似于“'5/7/2016'”这样的文字可以被解析为2016年7月5日或2016年5月7日，具体取决于各种设置，在一半的情况下会给出不正确（也许是未被注意到的）结果。或者会出现错误，例如“'12/31/2016'”（它是12月31日还是第31个月的12日？重要的不是你或我认为什么，而是特定的SQL Server实例认为什么，这取决于3或4个语言/数据库/连接设置）。 - ypercubeᵀᴹ

谢谢提供链接！关于“m/d/yyyy”和“d/m/yyyy”的观点非常好。作为一个美国人，我习惯于使用“m/d”，但这从来没有太多意义（难道不应该从小到大排列吗？）。Scott Hanselman提到他曾经讨论过9/11，他的德国朋友认为他指的是柏林墙倒塌的那个9/11：（[链接]（http://www.hanselman.com/blog/OnTheNightmareThatIsJSONDatesPlusJSONNETAndASPNETWebAPI.aspx） - James Toomey

Answer 2

您需要更加明确，同时添加开始和结束时间，包括毫秒数：

select * 
from xxx 
where dates between '2012-10-26 00:00:00.000' and '2012-10-27 23:59:59.997'

数据库可以很好地将'2012-10-27'解释为'2012-10-27 00:00:00.000'。

- Oded

---

实际上，对于 DATETIME 列，您需要指定 ..23:59:59.997，因为 DATETIME 具有 3 毫秒的分辨率精度。对于 DATETIME2，您的解决方案确实可以正常工作。 - marc_s

有足够多的眼睛，所有问题都变得肤浅了... :-) - marc_s

@marc 你是不是想说反了？这对于DATETIME2是*不起作用的。请参见select cast('20120101 12:34:56.9999999' as datetime2)。 - RichardTheKiwi

@RichardTheKiwi：是的 - Oded 最初使用了 ... 23:59:59.999，这对于 DATETIME2 是有效的（但对于 DATETIME 不是）。 - marc_s

Answer 3

这个表达的明确方式是（即将第二个日期增加1，并使其小于<）

select * 
from xxx 
where dates >= '20121026'
  and dates <  '20121028'

如果您使用的是SQL Server 2008或更高版本，则可以安全地将CAST转换为DATE，同时保留SARGability。例如：

select * 
from xxx 
where CAST(dates as DATE) between '20121026' and '20121027'

这明确告诉SQL Server，你只对“日期”部分感兴趣，以便将其与BETWEEN范围进行比较。

- RichardTheKiwi

Answer 4

您的问题并不是如何正确使用BETWEEN，而是请求帮助来处理意外截断的结果...

正如其他答案所提到/暗示的那样，问题在于您除了日期之外还有时间段。

根据我的经验，使用日期差值计算值得额外的键盘磨损。它允许您精确地表达您想要的，并且您会得到保障。

select * 
from xxx 
where datediff(d, '2012-10-26', dates) >=0
and  datediff(d, dates,'2012-10-27') >=0

使用datediff函数，如果第一个日期在第二个日期之前，您将得到一个正数。有几种编写上述内容的方法，例如始终先使用字段，然后再使用常量，或者仅仅翻转操作符。这是个人偏好的问题。

您可以明确说明您是否希望包含或排除端点，通过去掉一个或两个等号来实现。

BETWEEN函数在您的情况下可以使用，因为端点都假定为午夜（即DATE类型）。如果您的端点也是DATETIME类型，则使用BETWEEN函数可能需要更多的类型转换。在我看来，DATEDIFF函数被引入我们的生活中，就是为了使我们免受这些问题的影响。

- greg

Answer 5

试试这个：

select * 
from xxx 
where dates >= '2012-10-26 00:00:00.000' and dates <= '2012-10-27 23:59:59.997'

- krishnang

---

这不会选择日期为 2012-10-27 23:59:59.003 到 2012-10-27 23:59:59.997 的行 - 在该日期范围内可能还有一些行！ - marc_s

Answer 6

尝试使用以下查询。

select * 
from xxx 
where convert(date,dates) >= '2012-10-26' and convert(date,dates) <= '2012-10-27'

- Hiren Dhaduk