SQL Server：在两个日期之间获取记录？

In SQL I write a SELECT statement to fetch data between two dates, using between and

在SQL中，我编写了一个SELECT语句，用于在两个日期之间获取数据，使用和之间

Ex:

例如：

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

But the rows returned are for 26th only, not 26th and 27th.

但返回的行仅为第26行，而不是第26和第27行。

Can you help me? Thank you.

你可以帮我吗？谢谢。

6 个解决方案

#1

As others have answered, you probably have a DATETIME (or other variation) column and not a DATE datatype.

正如其他人已经回答的那样，您可能有DATETIME（或其他变体）列而不是DATE数据类型。

Here's a condition that works for all, including 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 has blogged about this at: What do BETWEEN and the devil have in common?

@Aaron Bertrand在博客中写道：BETWEEN和魔鬼有什么共同之处？

#2

You need to be more explicit and add the start and end times as well, down to the milliseconds:

您需要更明确地添加开始和结束时间，直到毫秒：

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

The database can very well interpret '2012-10-27' as '2012-10-27 00:00:00.000'.

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

#3

Your question didnt ask how to use BETWEEN correctly, rather asked for help with the unexpectedly truncated results...

你的问题没有问及如何正确使用BETWEEN，而是要求获得意外截断结果的帮助......

As mentioned/hinting at in the other answers, the problem is that you have time segments in addition to the dates.

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

In my experience, using date diff is worth the extra wear/tear on the keyboard. It allows you to express exactly what you want, and you are covered.

根据我的经验，使用日期差异值得在键盘上额外磨损。它可以让你准确地表达你想要的东西，并且你被覆盖了。

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

using datediff, if the first date is before the second date, you get a positive number. There are several ways to write the above, for instance always having the field first, then the constant. Just flipping the operator. Its a matter of personal preference.

使用datediff，如果第一个日期在第二个日期之前，则会得到一个正数。有几种方法可以编写上面的内容，例如总是首先使用字段，然后是常量。只是翻转操作员。这是个人喜好的问题。

you can be explicit about whether you want to be inclusive or exclusive of the endpoints by dropping one or both equal signs.

您可以通过删除一个或两个等号来明确表示您是想要包含还是排除端点。

BETWEEN will work in your case, because the endpoints are both assumed to be midnight (ie DATEs). If your endpoints were also DATETIME, using BETWEEN may require even more casting. In my mind DATEDIFF was put in our lives to insulate us from those issues.

BETWEEN将适用于您的情况，因为端点都被假定为午夜（即DATE）。如果你的终点也是DATETIME，那么使用BETWEEN可能需要更多的投射。在我看来，DATEDIFF被放在我们的生活中，以使我们免受这些问题的影响。

#4

The unambiguous way to write this is (i.e. increase the 2nd date by 1 and make it <)

写这个的明确方法是（即将第二个日期增加1并使其成为<）

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

If you're using SQL Server 2008 or above, you can safety CAST as DATE while retaining SARGability, e.g.

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

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

This explicitly tells SQL Server that you are only interested in the DATE portion of the dates column for comparison against the BETWEEN range.

这明确告诉SQL Server您只对date列的DATE部分感兴趣，以便与BETWEEN范围进行比较。

#5

Try this:

尝试这个：

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

#6

try to use following query

尝试使用以下查询

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

#1