Каков оптимальный способ сравнения дат на сервере Microsoft SQL?

У меня есть поле SQL datetime в очень большой таблице. Он проиндексирован и должен быть запрошен.

Проблема заключается в том, что SQL всегда хранит компонент времени (даже если он всегда полночь), но поиск выполняется в течение дня, а не времени.

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

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

Не вернет ничего, поскольку t.[DateColumn] всегда включает компонент времени.

Мой вопрос - лучший способ обойти это?

Кажется, есть две основные группы опций:

Создайте вторую переменную с помощью dateadd и используйте between ... and или >= ... and ... <=.
Преобразуйте t.[DateColumn] в компонент только для даты - я думаю, это приведет к игнорированию любых индексов.

Оба они кажутся очень грязными - я действительно не хочу проводить сравнение диапазона или сканировать таблицу.

Есть ли лучший способ?

Если один из этих вариантов является последовательно оптимальным, то как и почему?

Ответ 1

Преобразование в DATE или использование открытого диапазона дат в любом случае даст лучшую производительность. FYI, конвертируемые на сегодняшний день с использованием индекса, являются лучшими исполнителями. Больше тестирования различных методов в статье: Каков наиболее эффективный способ урезать время с 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)... происходит все медленнее.

Ответ 2

Вот пример:

У меня есть таблица заказов с полем 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'

Обнаружен здесь

Ответ 3

Вы можете добавить вычисляемый столбец, который включает только дату без времени. Между двумя вариантами я бы пошел с оператором BETWEEN, потому что он "чище" для меня и должен лучше использовать индексы. Сравнение планов выполнения показало бы, что BETWEEN будет быстрее; однако при фактическом тестировании они выполняли то же самое.

Ответ 4

Получить элементы, когда дата находится между fromdate и toDate.

где convert (date, fromdate, 103) <= '2016-07-26' и convert (date, toDate, 103) >= '2016-07-26'