SQL 在 TSQL 中检查两个日期时间是否在同一日历日的好方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's a good way to check if two datetimes are on the same calendar day in TSQL?
提问by Eric Z Beard
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.
这是我遇到的问题：我有一个大型查询，需要比较 where 子句中的日期时间以查看两个日期是否在同一天。我目前的解决方案很糟糕，是将日期时间发送到 UDF 以将它们转换为同一天的午夜，然后检查这些日期是否相等。当谈到查询计划时，这是一场灾难，几乎所有连接或 where 子句中的 UDF 也是如此。这是我的应用程序中仅有的一个我无法根除函数并为查询优化器提供它可以实际用来定位最佳索引的地方之一。
In this case, merging the function code back into the query seems impractical.
在这种情况下，将函数代码合并回查询似乎不切实际。
I think I am missing something simple here.
我想我在这里遗漏了一些简单的东西。
Here's the function for reference.
这里的功能供参考。
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
[Edit]
[编辑]
I'm incorporating @Todd's suggestion:
我正在合并@Todd 的建议：
where datediff(day, dateadd(hh, tz.Offset +
case when ds.LocalTimeZone is not null
then 1 else 0 end, t.TheDateINeedToCheck), @ActivityDate) = 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.
我对 datediff 如何工作的误解（连续年份的同一天产生 366，而不是我预期的 0）导致我浪费了很多精力。
But the query plan didn't change. I think I need to go back to the drawing board with the whole thing.
但是查询计划没有改变。我想我需要把整个事情都回到绘图板上。
回答by
This is much more concise:
这更简洁：
where
datediff(day, date1, date2) = 0
回答by Mark Brackett
You pretty much have to keep the left side of your where clause clean. So, normally, you'd do something like:
您几乎必须保持 where 子句的左侧干净。因此，通常情况下，您会执行以下操作：
WHERE MyDateTime >= @activityDateMidnight
AND MyDateTime < (@activityDateMidnight + 1)
(Some folks prefer DATEADD(d, 1, @activityDateMidnight) instead - but it's the same thing).
（有些人更喜欢 DATEADD(d, 1, @activityDateMidnight) 代替 - 但它是一样的）。
The TimeZone table complicates matter a bit though. It's a little unclear from your snippet, but it looks like t.TheDateInTable is in GMT with a Time Zone identifier, and that you're then adding the offset to compare against @activityDateMidnight - which is in local time. I'm not sure what ds.LocalTimeZone is, though.
但 TimeZone 表使事情变得有点复杂。从您的代码段中有点不清楚，但看起来 t.TheDateInTable 在 GMT 中带有时区标识符，然后您添加偏移量以与 @activityDateMidnight 进行比较 - 这是当地时间。不过，我不确定 ds.LocalTimeZone 是什么。
If that's the case, then you need to get @activityDateMidnight into GMT instead.
如果是这种情况，那么您需要将 @activityDateMidnight 改为 GMT。
回答by jason saldo
where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)
回答by SQLMenace
Make sure to read Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Codeso that you are sure that the optimizer can utilize the index effectively when messing with dates
确保仅在数据库中读取可以通过更改几行代码获得 1000% + 改进，以便您确保优化器在处理日期时可以有效地利用索引
回答by AlexCuse
this will remove time component from a date for you:
这将为您从日期中删除时间组件：
select dateadd(d, datediff(d, 0, current_timestamp), 0)
回答by Mark Brackett
Eric Z Beard:
埃里克 Z 胡子：
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.
我确实将所有日期存储在 GMT 中。这是用例：在美国东部时间 1 日晚上 11:00（格林威治标准时间 2 日）发生了一些事情。我想看 1 日的活动，而且我在美国东部时间，所以我想看晚上 11 点的活动。如果我只是比较原始 GMT 日期时间，我会错过一些东西。报告中的每一行都可以代表来自不同时区的活动。
Right, but when you say you're interested in activity for Jan 1st 2008 EST:
是的，但是当您说您对美国东部时间 2008 年 1 月 1 日的活动感兴趣时：
SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'
you just need to convert thatto GMT (I'm ignoring the complication of querying for the day before EST goes to EDT, or vice versa):
你只需要转换这为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
which should give you '1/1/2008 4:00 AM'. Then, you can just search in GMT:
这应该给你'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
The event in question is stored with a GMT EventTime of 1/2/2008 3:00 AM. You don't even need the TimeZone in the EventTable (for this purpose, at least).
有问题的事件以格林威治标准时间 2008 年 1 月 2 日凌晨 3:00 的 EventTime 存储。您甚至不需要 EventTable 中的 TimeZone （至少为此目的）。
Since EventTime is not in a function, this is a straight index scan - which should be pretty efficient. Make EventTime your clustered index, and it'll fly. ;)
由于 EventTime 不在函数中，因此这是直接索引扫描 - 这应该非常有效。将 EventTime 设为您的聚集索引，它就会飞起来。;)
Personally, I'd have the app convert the search time into GMT before running the query.
就个人而言，我希望应用程序在运行查询之前将搜索时间转换为 GMT。
回答by Mark Brackett
Eric Z Beard:
埃里克 Z 胡子：
the activity date is meant to indicate the local time zone, but not a specific one
活动日期旨在指示当地时区，但并非特定时区
Okay - back to the drawing board. Try this:
好的 - 回到绘图板。尝试这个：
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))
)
which will translate the @ActivityDate to local time, and compare against that. That's your best chance for using an index, though I'm not sure it'll work - you should try it and check the query plan.
这会将@ActivityDate 转换为本地时间，并与之进行比较。这是您使用索引的最佳机会，尽管我不确定它是否会起作用 - 您应该尝试并检查查询计划。
The next option would be an indexed view, with an indexed, computed TimeINeedToCheck in local time. Then you just go back to:
下一个选项是索引视图，在 local time具有索引的、计算的 TimeINeedToCheck 。然后你就回到：
where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)
which would definitely use the index - though you have a slight overhead on INSERT and UPDATE then.
这肯定会使用索引 - 尽管你在 INSERT 和 UPDATE 上有一点开销。
回答by Rad
You're spoilt for choice in terms of options here. If you are using Sybase or SQL Server 2008 you can create variables of type date and assign them your datetime values. The database engine gets rid of the time for you. Here's a quick and dirty test to illustrate (Code is in Sybase dialect):
在这里的选项方面，您被宠坏了。如果您使用的是 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'
For SQL 2005 and earlier what you can do is convert the date to a varchar in a format that does not have the time component. For instance the following returns 2008.08.22
对于 SQL 2005 及更早版本，您可以将日期转换为没有时间组件的格式的 varchar。例如以下返回 2008.08.22
select convert(varchar,'2008-08-22 18:11:14.133',102)
The 102 part specifies the formatting (Books online can list for you all the available formats)
102部分指定格式（在线书籍可以为您列出所有可用格式）
So, what you can do is write a function that takes a datetime and extracts the date element and discards the time. Like so:
因此，您可以做的是编写一个函数，该函数接受日期时间并提取日期元素并丢弃时间。像这样：
create function MakeDate (@InputDate datetime) returns datetime as
begin
return cast(convert(varchar,@InputDate,102) as datetime);
end
You can then use the function for companions
然后您可以将该功能用于同伴
Select * from Orders where dbo.MakeDate(OrderDate) = dbo.MakeDate(DeliveryDate)
回答by brendan
回答by Tundey
Regarding timezones, yet one more reason to store all dates in a single timezone (preferably UTC). Anyway, I think the answers using datediff, datepart and the different built-in date functions are your best bet.
关于时区，还有一个理由将所有日期存储在一个时区（最好是 UTC）中。无论如何，我认为使用 datediff、datepart 和不同的内置日期函数的答案是您最好的选择。
