在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]转换为仅日期组件-我认为这会导致任何索引被忽略。

这两个看起来都很混乱——我真的不想做一个范围比较或扫描表。

有更好的方法吗?

如果这些选项之一始终是最优的方式，那么如何以及为什么?

Answer 1

在任何情况下转换为DATE或使用开放式日期范围都将产生最佳性能。仅供参考，使用索引转换日期是性能最好的。在文章中对不同的技术进行更多的测试:从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)...都较慢。

Answer 2

下面是一个例子:

我有一个Order表，它的DateTime字段叫做OrderDate。我想检索订单日期等于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'

Answer 3

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

Answer 4

获取日期介于fromdate和toDate之间的项。

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