在Microsoft SQL Server中比较日期的最佳方法是什么？

Question

我有一个非常大的表，其中包含一个SQL datetime字段。该字段已经被索引并需要进行查询。

问题在于SQL始终存储时间组件（即使它总是午夜），但搜索是按天而不是时间进行的。

declare @dateVar datetime = '2013-03-11;

select t.[DateColumn]
from MyTable t
where t.[DateColumn] = dateVar;

由于t.[DateColumn]始终包含时间组件，因此不会返回任何内容。

我的问题是如何解决这个问题？

似乎有两大主要选项：

1. 使用dateadd创建第二个变量，并使用between ... and或>= ... and ... <=。

2. 将t.[DateColumn]转换为仅包含日期的组件 - 我认为这将导致忽略任何索引。

这两种方法都非常混乱 - 我真的不想进行范围比较或扫描表。

是否有更好的方法？

如果其中一种选项始终是最佳方式，那么如何以及为什么？

- Keith

Answer 1

转换为 DATE 或在任何情况下使用开放式日期范围将产生最佳性能。值得注意的是，使用索引进行日期转换是最优秀的表现方式。有关更多测试和不同技术的信息，请参见本文：What is the most efficient way to trim time from datetime?，作者是 Aaron Bertrand。

从那篇文章中可以得出：

DECLARE @dateVar datetime = '19700204';

-- Quickest when there is an index on t.[DateColumn], 
-- because CONVERT can still use the index.
SELECT t.[DateColumn]
FROM MyTable t
WHERE = CONVERT(DATE, t.[DateColumn]) = CONVERT(DATE, @dateVar);

-- Quicker when there is no index on t.[DateColumn]
DECLARE @dateEnd datetime = DATEADD(DAY, 1, @dateVar);
SELECT t.[DateColumn] 
FROM MyTable t
WHERE t.[DateColumn] >= @dateVar AND 
      t.[DateColumn] < @dateEnd;

同样来自那篇文章：使用 BETWEEN、DATEDIFF 或 CONVERT(CHAR(8)... 都比较慢。

- Aleksandr Fedorenko

Answer 2

以下是一个示例：

我有一个名为Order的表，其中有一个名为OrderDate的DateTime字段。我想检索所有订单，其中订单日期等于01/01/2006。有以下几种方法可以实现：

1) WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
2) WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
3) WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
4) WHERE OrderDate LIKE '01/01/2006%'
5) WHERE OrderDate >= '01/01/2006'  AND OrderDate < '01/02/2006'

这里找到了 here

- paraselena

---

在所有这些中，#1 被认为是最有效的：http://www.sqlservercentral.com/Forums/Topic937765-338-1.aspx - RandomUs1r

@RandomUs1r，选项＃1需要对表中的每一行运行datediff计算，这肯定不是最快或最有效的方法。选项＃2也是如此，因为它需要进行varchar（20）转换。选项＃4和＃5更有前途，我还会添加两个：6）where OrderDate between @dateVar and dateadd(day, 1, @dateVar)和7）WHERE convert(date, OrderDate) = @dateVar。 - Keith

这5个建议中，1-4方法都非常慢。第5种方法对于有堆积的表格来说很快，而最快的方法（使用convert(date, OrderDate)，详见所选答案）甚至不在列表中。 - Keith

Answer 3

您可以添加一个计算列，仅包含日期而不包括时间。在这两个选项之间，我会选择BETWEEN运算符，因为它对我来说更加“干净”，并且应该更好地利用索引。比较执行计划似乎表明BETWEEN会更快；然而，在实际测试中，它们的表现相同。

- Michael L.

---

事实证明，在这里使用 between 是个坏主意。参考链接。 - Keith

@Keith +1 感谢提供链接。我已经知道大部分问题（我知道它是包容性的，当需要时我会习惯性地在结束日期添加时间值），但最后几个问题对我来说是新的。在工作中，我们选择的所有时间值都被截断为分钟或秒（不，那不是我的决定。是的，缺乏精度很烦人），所以纳秒问题还没有出现，但它们可能足够严重，我可能会今天与其他开发人员讨论将BETWEEN的使用更改为=>...<。 - Michael L.

Answer 4

当日期在fromdate和toDate之间时获取项目。

其中 convert(date, fromdate, 103 ) <= '2016-07-26' and convert(date, toDate, 103) >= '2016-07-26'

- gaze

---

嗨 @gaze，欢迎来到 Stack Overflow。你的回答在这里并没有真正添加任何内容 - 如果你阅读了被接受的答案，你会发现他们已经涵盖了 convert 并解释了基于索引更改类型或使用哪种方法更好，这本质上就是我在问的。要获得赞同，你需要添加一些其他人没有的东西，或者第一个回答。 - Keith