Какой хороший способ проверить, являются ли два дня в одном календарном дне в TSQL?

Вот проблема, с которой я сталкиваюсь: у меня есть большой запрос, который должен сравнивать datetimes в предложении where, чтобы увидеть, совпадают ли две даты в один день. Мое текущее решение, которое отстойно, заключается в том, чтобы отправить данные в UDF, чтобы преобразовать их в полночь того же дня, а затем проверить эти даты для равенства. Когда дело доходит до плана запроса, это катастрофа, как и почти все UDF в соединениях или в тех случаях, когда предложения. Это одно из единственных мест в моем приложении, что я не смог искоренить функции и дать оптимизатору запросов что-то, что он действительно может использовать, чтобы найти лучший индекс.

В этом случае слияние кода функции обратно в запрос кажется непрактичным.

Я думаю, что мне не хватает чего-то простого здесь.

Здесь функция для ссылки.

if not exists (select * from dbo.sysobjects 
              where id = object_id(N'dbo.f_MakeDate') and               
              type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  exec('create function dbo.f_MakeDate() returns int as 
         begin declare @retval int return @retval end')
go

alter function dbo.f_MakeDate
(
    @Day datetime, 
    @Hour int, 
    @Minute int
)
returns datetime
as

/*

Creates a datetime using the year-month-day portion of @Day, and the 
@Hour and @Minute provided

*/

begin

declare @retval datetime
set @retval = cast(
    cast(datepart(m, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(d, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(yyyy, @Day) as varchar(4)) + 
    ' ' + 
    cast(@Hour as varchar(2)) + 
    ':' + 
    cast(@Minute as varchar(2)) as datetime)
return @retval
end

go

Чтобы усложнить ситуацию, я присоединяюсь к таблицам часовых поясов, чтобы проверить дату по местному времени, которая может быть различной для каждой строки:

where 
dbo.f_MakeDate(dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), 0, 0) = @activityDateMidnight

[изменить]

Я включаю предложение @Todd:

where datediff(day, dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), @ActivityDate) = 0

Мое заблуждение о том, как работает fariff (в тот же день года в течение последовательных лет дает 366, а не 0, как я ожидал) заставил меня потратить много усилий.

Но план запроса не изменился. Думаю, мне нужно вернуться на чертежную доску со всем этим.

Ответы

Ответ 1

Это гораздо более краткий:

where 
  datediff(day, date1, date2) = 0

Ответ 2

Вы в значительной степени должны убрать левую часть вашего места, где есть статья. Итак, вы обычно делаете что-то вроде:

WHERE MyDateTime >= @activityDateMidnight 
      AND MyDateTime < (@activityDateMidnight + 1)

(Некоторые люди предпочитают DATEADD (d, 1, @activityDateMidnight) вместо этого, но это то же самое).

Таблица TimeZone немного усложняет ситуацию. Это немного непонятно из вашего фрагмента, но похоже, что t.TheDateInTable находится в GMT с идентификатором временной зоны и что вы затем добавляете смещение для сравнения с @activityDateMidnight - которое находится по местному времени. Я не уверен, что такое ds.LocalTimeZone.

Если это так, тогда вам нужно вместо @activityDateMidnight в GMT.

Ответ 3

where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)

Ответ 4

Обязательно прочитайте Только в базе данных вы можете получить 1000% + улучшение, изменив несколько строк кода, чтобы вы были уверены что оптимизатор может эффективно использовать индекс, когда возится с датами

Ответ 5

Эрик Борд:

Я сохраняю все даты в GMT. Здесь прецедент: что-то произошло в 11:00 по восточному стандартному времени на 1-м, что является вторым по Гринвичу. Я хочу видеть активность для 1-го, и я нахожусь в EST, поэтому я хочу увидеть активность 11PM. Если бы я просто сравнивал время с GMT GMT, я бы пропустил все. Каждая строка в отчете может представлять собой активность из другого часового пояса.

Правильно, но когда вы говорите, что вы заинтересованы в активности на 1 января 2008 года EST:

SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'

вам просто нужно преобразовать это в GMT (я игнорирую сложность запросов за день до того, как EST перейдет на EDT или наоборот):

Table: TimeZone
Fields: TimeZone, Offset
Values: EST, -4

--Multiply by -1, since we're converting EST to GMT.
--Offsets are to go from GMT to EST.
SELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)
FROM TimeZone
WHERE TimeZone = @activityDateTZ

который должен дать вам "1/1/2008 4:00 AM". Затем вы можете просто искать в GMT:

SELECT * FROM EventTable
WHERE 
   EventTime >= @activityGmtBegin --1/1/2008 4:00 AM
   AND EventTime < (@activityGmtBegin + 1) --1/2/2008 4:00 AM

Соответствующее событие хранится с GMT EventTime 1/2/2008 3:00 AM. Вам даже не требуется TimeZone в EventTable (для этой цели, по крайней мере).

Так как EventTime не находится в функции, это прямое сканирование индекса, которое должно быть довольно эффективным. Сделайте EventTime своим кластеризованным индексом, и он будет летать.;)

Лично я хотел бы, чтобы приложение конвертировало время поиска в GMT перед запуском запроса.

Ответ 6

это удалит компонент времени из даты для вас:

select dateadd(d, datediff(d, 0, current_timestamp), 0)

Ответ 7

Эрик Борд:

дата активности предназначена для указания локального часового пояса, но не конкретного

Хорошо - вернитесь к чертежной доске. Попробуйте следующее:

where t.TheDateINeedToCheck BETWEEN (
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, @ActivityDate)
    AND
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, (@ActivityDate + 1))
)

который переведёт @ActivityDate в локальное время и сравним с этим. Это ваш лучший шанс использовать индекс, хотя я не уверен, что он сработает - вы должны попробовать его и проверить план запроса.

Следующим вариантом будет индексированное представление с индексированным, вычисленным TimeIneedToCheck по местному времени. Затем вы просто вернетесь к:

where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)

который определенно будет использовать индекс, хотя у вас есть небольшие накладные расходы на INSERT и UPDATE.

Ответ 8

Вы избалованы от выбора вариантов. Если вы используете Sybase или SQL Server 2008, вы можете создавать переменные типа date и присваивать им свои значения datetime. Механизм базы данных избавляет вас от времени. Здесь быстрый и грязный тест, чтобы проиллюстрировать (код находится в диалекте Sybase):

declare @date1 date
declare @date2 date
set @date1='2008-1-1 10:00'
set @date2='2008-1-1 22:00'
if @[email protected]
    print 'Equal'
else
    print 'Not equal'

Для SQL 2005 и более ранних, что вы можете сделать, это преобразовать дату в varchar в формате, который не имеет компонента времени. Например, следующие сообщения 2008.08.22

select convert(varchar,'2008-08-22 18:11:14.133',102)

Часть 102 определяет форматирование (в онлайн-книгах для вас доступны все доступные форматы)

Итак, что вы можете сделать, это написать функцию, которая принимает дату-время и извлекает элемент даты и отбрасывает время. Например:

create function MakeDate (@InputDate datetime) returns datetime as
begin
    return cast(convert(varchar,@InputDate,102) as datetime);
end

Затем вы можете использовать функцию для компаньонов

Select * from Orders where dbo.MakeDate(OrderDate) = dbo.MakeDate(DeliveryDate)

Ответ 9

Я использовал бы функцию dayofyear datepart:


Select *
from mytable
where datepart(dy,date1) = datepart(dy,date2)
and
year(date1) = year(date2) --assuming you want the same year too

См. справочную дату date здесь.

Ответ 10

Что касается часовых поясов, но еще одна причина хранить все даты в одном часовом поясе (желательно UTC). Во всяком случае, я думаю, что ваши ответы с использованием lateiff, datepart и разных встроенных функций даты - ваш лучший выбор.