在Microsoft SQL Server中比较日期的最佳方法是什么？ [英] What is the optimal way to compare dates in Microsoft SQL server?

问题描述

我在一个非常大的表中有一个SQL datetime 字段。它被索引并需要查询。

I have a SQL datetime field in a very large table. It's indexed and needs to be queried.

问题是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.

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

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

不会返回任何东西，如 t。[DateColumn] code>总是包含一个时间分量。

Won't return anything, as the t.[DateColumn] always includes a time component.

我的问题是最好的方法是什么？

My question is what is the best way round this?

似乎有两个主要的选项组：

There seem to be two main groups of options:

1. 使用 dateadd创建第二个变量，并使用...之间的和或> = ...和...< = 。

1. Create a second variable using dateadd and use a between ... and or >= ... and ... <=.

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

Convert the t.[DateColumn] into a date-only component - I think this will cause any indexes to be ignored.

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

Both of these seem very messy - I don't really want to be making a range comparison or scan the table.

有更好的方法吗？

如果这些选项之一一致

推荐答案

转换为 DATE 或在任何情况下使用开放日期范围将产生最佳性能。 FYI，转换日期使用指数是表现最好的。更多测试文章中的不同技术：从datetime修剪时间的最有效的方法是什么？发布者：Aaron Bertrand

Converting to a DATE or 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

从那篇文章：

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）... 都比较慢。

Also from that article: using BETWEEN, DATEDIFF or CONVERT(CHAR(8)... are all slower.

这篇关于在Microsoft SQL Server中比较日期的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！