在TSQL中检查两个日期时间是否在同一日历日的好方法是什么？
这是我遇到的问题：我有一个大型查询，需要比较where子句中的日期时间以查看两个日期是否在同一天。我当前的解决方法很糟糕，就是将日期时间发送到UDF中，以将其转换为同一天的午夜，然后检查这些日期是否相等。当涉及到查询计划时，这是一场灾难，联接或者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)，这使我浪费了很多精力。
但是查询计划没有改变。我想我需要把整个事情都回到图纸上。
解决方案
回答
where year(date1) = year(date2) and month(date1) = month(date2) and day(date1) = day(date2)
回答
这更加简洁：
where datediff(day, date1, date2) = 0
回答
这将从日期中删除时间部分：
select dateadd(d, datediff(d, 0, current_timestamp), 0)
回答
我们几乎必须保持where子句的左侧干净。因此，通常情况下，我们会执行以下操作：
WHERE MyDateTime >= @activityDateMidnight
AND MyDateTime < (@activityDateMidnight + 1)
(有些人更喜欢DATEADD(d，1，@activityDateMidnight)，但这是同一回事。)
TimeZone表使事情变得有些复杂。从代码片段中还不清楚，但是看起来像t.TheDateInTable在GMT中带有时区标识符，然后我们要添加偏移量以与本地时间的@activityDateMidnight进行比较。我不确定ds.LocalTimeZone是什么。
如果是这样，那么我们需要将@activityDateMidnight放入GMT。
回答
确保仅读取数据库中的内容，才能通过更改几行代码来获得1000％以上的改进，从而确保优化器在处理日期混乱时可以有效利用索引
回答
在这里，我们对选择的选择一无所获。如果使用的是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 @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部分指定格式(在线图书可以为我们列出所有可用格式)
因此，我们可以做的是编写一个函数，该函数采用datetime并提取date元素并丢弃时间。像这样：
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)
回答
我将使用datepart的dayofyear函数：
Select * from mytable where datepart(dy,date1) = datepart(dy,date2) and year(date1) = year(date2) --assuming you want the same year too
请参阅此处的日期部分参考。
回答
关于时区，还有另一个理由将所有日期存储在一个时区(最好是UTC)。无论如何，我认为使用datediff，datepart和其他内置日期函数的答案是最好的选择。
回答
埃里克·比尔(Eric Z Beard)：
I do store all dates in GMT. Here's the use case: something happened at 11:00 PM EST on the 1st, which is the 2nd GMT. I want to see activity for the 1st, and I am in EST so I will want to see the 11PM activity. If I just compared raw GMT datetimes, I would miss things. Each row in the report can represent an activity from a different time zone.
是的，但是当我们说我们对2008年1月1日美国东部时间的活动感兴趣时：
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
所讨论的事件的存储时间为格林尼治标准时间EventTime 1/2/2008 3:00 AM。我们甚至不需要在EventTable中使用TimeZone(至少为此目的)。
由于EventTime不在函数中，因此这是直接索引扫描，应该非常有效。将EventTime设为聚集索引，它将开始运行。 ;)
就个人而言，我希望该应用在运行查询之前将搜索时间转换为GMT。
回答
Eric Z Beard：
the activity date is meant to indicate the local time zone, but not a specific one
好回到绘图板。试试这个：
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上有一些开销，但肯定会使用索引。
