提问人：Eric Z Beard 提问时间：8/22/2008 最后编辑：RikalousEric Z Beard 更新时间：7/22/2010 访问量：62756
在TSQL中检查两个日期时间是否在同一个日历日的好方法是什么？
What's a good way to check if two datetimes are on the same calendar day in TSQL?
问：
这是我遇到的问题：我有一个大查询，需要比较 where 子句中的日期时间，以查看两个日期是否在同一天。我目前的解决方案很糟糕，是将日期时间发送到 UDF 中以将它们转换为同一天的午夜，然后检查这些日期是否相等。当涉及到查询计划时，这是一场灾难，几乎所有的 UDF in 联接或 where 子句也是如此。这是我的应用程序中唯一无法根除函数并为查询优化器提供实际可以用来查找最佳索引的地方之一。
在这种情况下，将函数代码合并回查询似乎是不切实际的。
我想我在这里错过了一些简单的东西。
以下是供参考的函数。
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）导致我浪费了很多精力。
但查询计划没有改变。我想我需要回到整个事情的绘图板。
答：
6赞
jason saldo
8/22/2008
#1
where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)
85赞
Todd Tingen
8/22/2008
#2
这要简洁得多：
where
datediff(day, date1, date2) = 0
评论
8赞
ErikE
9/13/2010
为什么这有 9 个赞成票并获得了答案？它简明扼要，好吧，并导致 OP 走上了错误的道路。由于第二个日期 @ActivityDate 是固定的，我们可以将数学移动到右侧并获得更好的性能。
3赞
DForck42
9/13/2010
同意@emtucifor。SQL Server 无法提供此逻辑。
0赞
Joyce
8/26/2017
@ErikE，我不太明白你的意思，你有代码片段吗？
2赞
ErikE
8/26/2017
@Joyce，看看马克·布兰克特的回答。他本身就被放在任何数学运算符的一边。这意味着查询引擎可以执行范围搜索，并直接在 b 树中导航到介于两个时间之间的行。有了这个可怕的、可怕的、可怕的建议，表格中的每一行都必须对它进行数学运算，这是一种扫描，对性能来说是可怕的。如果表有 1 亿行，只有 10 行符合条件，那么想想看所有行才能找到 10 行是多么糟糕!!!!!MyDateTime
2赞
AlexCuse
8/22/2008
#3
这将从日期中删除时间组件：
select dateadd(d, datediff(d, 0, current_timestamp), 0)
21赞
Mark Brackett
8/22/2008
#4
你几乎必须保持你的 where 子句的左侧干净。所以，通常情况下，你会做这样的事情：
WHERE MyDateTime >= @activityDateMidnight
AND MyDateTime < (@activityDateMidnight + 1)
（有些人更喜欢 DATEADD（d， 1， @activityDateMidnight） - 但这是一回事）。
不过，TimeZone 表使问题变得有点复杂。从您的代码片段中有点不清楚，但看起来 t.TheDateInTable 在 GMT 中带有时区标识符，然后您正在添加偏移量以与@activityDateMidnight进行比较 - 这是当地时间。我不确定 ds 是什么。不过，LocalTimeZone 是。
如果是这种情况，那么您需要将@activityDateMidnight进入 GMT。
1赞
Rad
8/22/2008
#5
就这里的选择而言，您被宠坏了。如果您使用的是 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)
0赞
brendan
8/22/2008
#6
我会使用 datepart 的 dayofyear 函数：
Select *
from mytable
where datepart(dy,date1) = datepart(dy,date2)
and
year(date1) = year(date2) --assuming you want the same year too
请参阅此处的 datepart 参考。
2赞
Mark Brackett
8/23/2008
#7
埃里克·比尔德（Eric Z Beard）：
我确实将所有日期都存储在格林威治标准时间中。这是用例：美国东部时间 1 日晚上 11：00 发生了一些事情，即格林威治标准时间 2 日。我想查看 1 日的活动，我在 EST，所以我想查看晚上 11 点的活动。如果我只是比较原始的格林威治标准时间日期，我会错过一些东西。报表中的每一行都可以表示来自不同时区的活动。
是的，但是当您说您对 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
相关事件的 GMT EventTime 存储为 2008 年 1 月 2 日凌晨 3：00。您甚至不需要 EventTable 中的 TimeZone（至少用于此目的）。
由于 EventTime 不在函数中，因此这是一个直接的索引扫描 - 这应该非常有效。将 EventTime 设为聚集索引，它就会飞起来。;)
就个人而言，我会让应用程序在运行查询之前将搜索时间转换为 GMT。
评论
0赞
Felipe Rodriguez Herrera
6/1/2021
如果改用 BETWEEN 语句，会产生负面影响吗？
0赞
Mark Brackett
6/17/2021
@FelipeRodriguez - 不是真的;请注意，BETWEEN 在两端都是包含的，因此可能会出现重叠范围。
1赞
Mark Brackett
8/23/2008
#8
埃里克·比尔德（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转换为当地时间，并与之进行比较。这是使用索引的最佳机会，尽管我不确定它是否有效 - 您应该尝试并检查查询计划。
下一个选项是索引视图，其中包含本地时间的索引、计算的 TimeINeedToCheck。然后你只需返回：
where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)
这肯定会使用索引 - 尽管您在 INSERT 和 UPDATE 上有轻微的开销。
评论