在TSQL中检查两个日期时间是否在同一日历日的好方法是什么？ [英] What's a good way to check if two datetimes are on the same calendar day in TSQL?

问题描述

这是我遇到的问题：我有一个大型查询，需要比较where子句中的日期时间以查看两个日期是否在同一天。我当前的解决方法很糟糕，是将日期时间发送到UDF中，以将其转换为同一天的午夜，然后检查这些日期是否相等。当涉及到查询计划时，这是一场灾难，联接或where子句中的几乎所有UDF都是如此。这是我的应用程序中唯一无法根除函数并为查询优化器提供一些可以实际用来定位最佳索引的地方之一。

Here is the issue I am having: I have a large query that needs to compare datetimes in the where clause to see if two dates are on the same day. My current solution, which sucks, is to send the datetimes into a UDF to convert them to midnight of the same day, and then check those dates for equality. When it comes to the query plan, this is a disaster, as are almost all UDFs in joins or where clauses. This is one of the only places in my application that I haven't been able to root out the functions and give the query optimizer something it can actually use to locate the best index.

在这种情况下，将功能代码合并回查询中似乎是不切实际的。

In this case, merging the function code back into the query seems impractical.

我认为我在这里缺少一些简单的东西。

I think I am missing something simple here.

这里是参考功能。

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

为了使事情变得复杂，我加入时区表以对照当地时间检查日期，每行的日期可能不同：

To complicate matters, I am joining on time zone tables to check the date against the local time, which could be different for every row:

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的建议：

I'm incorporating @Todd's suggestion:

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

我对datediff的工作方式的误解（连续多年的同一天产生366，而不是我预期的0）使我浪费了很多精力。

My misconception about how datediff works (the same day of year in consecutive years yields 366, not 0 as I expected) caused me to waste a lot of effort.

但是查询计划没有改变。我想我需要把整个事情都回头看。

But the query plan didn't change. I think I need to go back to the drawing board with the whole thing.

推荐答案

这更加简洁：

where 
  datediff(day, date1, date2) = 0

这篇关于在TSQL中检查两个日期时间是否在同一日历日的好方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！