在 Microsoft SQL Server 中比较日期的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15344661/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the optimal way to compare dates in Microsoft SQL server?
提问by Keith
I have a SQL datetimefield in a very large table. It's indexed and needs to be queried.
我datetime在一个非常大的表中有一个 SQL字段。它已编入索引，需要查询。
The problem is that SQL always stores the time component (even though it's always midnight), but the searches are to the day, rather than time.
问题是 SQL 总是存储时间组件（即使它总是午夜），但搜索是针对当天而不是时间。
declare @dateVar datetime = '2013-03-11;
select t.[DateColumn]
from MyTable t
where t.[DateColumn] = dateVar;
Won't return anything, as the t.[DateColumn]always includes a time component.
不会返回任何东西，因为它t.[DateColumn]总是包含一个时间组件。
My question is what is the best way round this?
我的问题是最好的方法是什么？
There seem to be two main groups of options:
似乎有两组主要选项：
Create a second variable using
dateaddand use abetween ... andor>= ... and ... <=.Convert the
t.[DateColumn]into a date-only component - I think this will cause any indexes to be ignored.
创建第二个变量
dateadd并使用 abetween ... andor>= ... and ... <=。将其转换
t.[DateColumn]为仅限日期的组件 - 我认为这会导致任何索引被忽略。
Both of these seem very messy - I don't really want to be making a range comparison or scan the table.
这两个看起来都很混乱 - 我真的不想进行范围比较或扫描表格。
Is there a better way?
有没有更好的办法？
If one of these options is consistently optimal way then how and why?
如果这些选项之一始终是最佳方式，那么如何以及为什么？
回答by Aleksandr Fedorenko
Converting to a DATEor using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime?Posted by Aaron Bertrand
DATE在任何情况下转换为开放式日期范围或使用开放式日期范围都会产生最佳性能。仅供参考，使用索引转换为日期是表现最好的。在文章中更多地测试不同的技术：从日期时间修剪时间的最有效方法是什么？亚伦·伯特兰 (Aaron Bertrand) 发表
From that article:
从那篇文章：
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;
Also from that article: using BETWEEN, DATEDIFFor CONVERT(CHAR(8)...are all slower.
同样来自那篇文章：使用BETWEEN, DATEDIFForCONVERT(CHAR(8)...都较慢。
回答by paraselena
Here is an example:
下面是一个例子：
I've an Order table with a DateTime field called OrderDate. I want to retrieve all orders where the order date is equals to 01/01/2006. there are next ways to do it:
我有一个带有名为 OrderDate 的 DateTime 字段的 Order 表。我想检索订单日期等于 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'
Is found here
在这里找到
回答by Michael L.
You could add a calculated column that includes only the date without the time. Between the two options, I'd go with the BETWEENoperator because it's 'cleaner' to me and should make better use of indexes. Comparing execution plans would seem to indicate that BETWEENwould be faster; however, in actual testing they performed the same.
您可以添加一个仅包含日期而不包含时间的计算列。在这两个选项之间，我会选择BETWEEN运营商，因为它对我来说“更干净”并且应该更好地利用索引。比较执行计划似乎表明这BETWEEN会更快；然而，在实际测试中，它们的表现相同。
回答by gaze
Get items when the date is between fromdate and toDate.
获取日期介于 fromdate 和 toDate 之间的项目。
where convert(date, fromdate, 103 ) <= '2016-07-26' and convert(date, toDate, 103) >= '2016-07-26'
其中 convert(date, fromdate, 103 ) <= '2016-07-26' and convert(date, toDate, 103) >= '2016-07-26'
