代码之家 › 专栏 › 技术社区 › Eric Z Beard

在TSQL中,检查两个日期时间是否在同一个日历日的好方法是什么?

user-defined-functions datetime tsql sql-server sql

Eric Z Beard · 技术社区 · 16 年前

这是我面临的问题:我有一个很大的查询,需要在WHERE子句中比较日期时间,以查看两个日期是否在同一天。我目前的解决方案是将日期时间发送到UDF中,将其转换为当天的午夜,然后检查这些日期是否相等,这很糟糕。当涉及到查询计划时,这是一个灾难,几乎所有join或where子句中的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

我对datediff如何工作的误解(连续几年中的同一天产生366个,而不是我预期的0个)导致我浪费了很多精力。

但是查询计划没有改变。我想我需要把整个事情都回到画板上来。

10 回复 | 直到 16 年前

Todd Tingen 16 年前

这要简洁得多:

where 
  datediff(day, date1, date2) = 0

Mark Brackett Achilles Ram Nakirekanti 16 年前

你几乎必须保持WHERE条款的左边是干净的。所以,通常情况下,你会做如下的事情:

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

(有些人更喜欢dateadd(d,1,@activitydatemnight),但这是相同的事情)。

不过时区表让事情有点复杂。您的代码片段中有点不清楚,但看起来有点不清楚。该表是在带有时区标识符的格林威治标准时间内,然后您要添加偏移量以与本地时间内的@activitydateMidnight进行比较。不过,我不确定ds.localtimezone是什么。

如果是这样,那么您需要将@activitydatemnight改为gmt。

jason saldo 16 年前

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

SQLMenace 16 年前

确保阅读 Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code 这样您就可以确保优化器在处理日期时能够有效地利用索引。

Mark Brackett Achilles Ram Nakirekanti 16 年前

Eric Z Beard:

我把所有的日期都存储在格林威治标准时间内。这里是一个用例:在东部时间1点11:00发生了一些事情,也就是说在格林威治标准时间2点。我想看到第一个活动,我在东部时间,所以我想看到11点的活动。如果我只是比较原始的格林尼治标准时间,我会错过一些事情。报表中的每一行可以表示来自不同时区的活动。

是的,但是当你说你对2008年1月1日的EST活动感兴趣时:

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

你只需要转换那个到格林威治标准时间(我忽略了查询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

应该是“2008年1月1日凌晨4:00”。然后,您只需在GMT中搜索:

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

相关事件存储在格林威治标准时间2008年1月2日凌晨3:00。您甚至不需要事件表中的时区(至少为此目的)。

因为eventtime不在函数中,所以这是一个直接的索引扫描——应该非常有效。让eventtime成为聚集索引,它就会飞走。;)

就我个人而言,我希望应用程序在运行查询之前将搜索时间转换为gmt。

AlexCuse 16 年前

这将从日期中删除时间组件:

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

Rad 16 年前

你在这里的选择权被宠坏了。如果您使用Sybase或SQL Server 2008,则可以创建日期类型的变量,并为其分配日期时间值。数据库引擎为您省去了时间。下面是一个快速而肮脏的测试(代码使用Sybase方言):

declare @date1 date
declare @date2 date
set @date1='2008-1-1 10:00'
set @date2='2008-1-1 22:00'
if @date1=@date2
    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)

Mark Brackett Achilles Ram Nakirekanti 16 年前

Eric Z Beard:

活动日期是指本地时区,但不是特定时区。

好的-回到画板上。试试这个:

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转换为本地时间,并与之进行比较。这是您使用索引的最佳机会,尽管我不确定它是否有效-您应该尝试它并检查查询计划。

下一个选项是一个索引视图,带有一个索引的、计算的TimelineTocheck 在当地时间 . 然后你回到:

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

它肯定会使用索引——尽管在插入和更新时会有一些开销。

brendan 16 年前

我将使用datepart的dayofyear函数:


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

参见日期部分参考 here .

Tundey 16 年前

关于时区,还有一个理由可以在一个时区(最好是UTC)中存储所有日期。不管怎样,我认为使用datediff、datepart和不同的内置日期函数的答案是最好的选择。