比较 T-SQL 中的日期，忽略时间部分

Compare dates in T-SQL, ignoring the time part

提问by Fiona - myaccessible.website

I'm using MS SQL 2005, and I want to check two dates for equality, but ignoring the time part.

我正在使用 MS SQL 2005，我想检查两个日期是否相等，但忽略时间部分。

I know I can make use of DATEDIFF, but am concerned that it may be slow - this SP gets used in the DB a lot!

我知道我可以使用DATEDIFF，但我担心它可能会很慢 - 这个 SP 在数据库中得到了很多使用！

Any suggestions?

有什么建议？

Edit:David Andres' comment:

编辑：大卫安德烈斯的评论：

'"comparison" includes much more than equality'

““比较”不仅仅包括平等”

让我意识到我没有把我的问题说得足够清楚——我实际上只是在检查平等，仅此而已。

回答by Joel Coehoorn

The most reasonable way to do this is to strip away the time portion of the datetime values and compare the results, and the best way to strip the time portion from a datetime is like this:

最合理的方法是剥离日期时间值的时间部分并比较结果，从日期时间剥离时间部分的最佳方法是这样的：

cast(current_timestamp as date)    

I used to use and advocate a process that looked like one of the following two lines:

我曾经使用和提倡一个看起来像以下两行之一的过程：

cast(floor(cast(getdate() as float)) as datetime)
dateadd(dd,0, datediff(dd,0, getDate()))

But now that Sql Server has the Datetype, which does not hold a time component, there is little reason to use either of those techniques.

但是既然 Sql Server 具有Date不包含时间组件的类型，那么几乎没有理由使用这两种技术中的任何一种。

One more thing to keep in mind is this will still bog down a query if you need to do it for two datetime values for every row in a where clause or join condition. If possible you want to factor this out somehow so it's pre-computed as much as possible, for example using a view or computed column.

要记住的另一件事是，如果您需要为 where 子句或连接条件中的每一行的两个日期时间值执行此操作，这仍然会使查询陷入困境。如果可能，您想以某种方式将其分解，以便尽可能地预先计算它，例如使用视图或计算列。

Finally, note the DATEDIFF function compares the number of boundaries crossed. This means the datediff in days between '2009-09-14 11:59:59'and '2009-09-15 00:00:01'is 1, even though only 2 seconds has elapsed, but the DATEDIFF in days between '2009-09-15 00:00:01'and '2009-09-15 11:59:59'is still zero, even though 86,398 seconds elapsed. It doesn't really care at all about the time portion there, only the boundaries. Depending on what your query is trying to do, you might be able to use that to your advantage.

最后，请注意 DATEDIFF 函数比较跨越边界的数量。这意味着'2009-09-14 11:59:59'和之间的日期差异'2009-09-15 00:00:01'为 1，即使只过去了 2 秒，但'2009-09-15 00:00:01'和之间的天数中的 DATEDIFF'2009-09-15 11:59:59'仍然为零，即使过去了 86,398 秒。它根本不关心那里的时间部分，只关心边界。根据您的查询尝试执行的操作，您或许可以利用它来发挥自己的优势。

回答by pete

WHERE DATEDIFF(day, date1, date2)=0

WHERE DATEDIFF(day, date1, date2)=0

回答by David Andres

In my own work, when I wanted to determine that two dates were equal irrespective of time of day, I've used the following:

在我自己的工作中，当我想确定两个日期在一天中的任何时间都相等时，我使用了以下内容：

WHERE CONVERT(VARCHAR, date1, 101) = CONVERT(VARCHAR, date2, 101)

Granted, "comparison" includes much more than equality and the above converts the dates to U.S.A format MM/DD/YYYY prior to making the comparison. So, performance implications and inability to compare date differences.

诚然，“比较”不仅仅包括相等，而且在进行比较之前，上面将日期转换为美国格式 MM/DD/YYYY。因此，性能影响和无法比较日期差异。

But...it does work.

但是……确实有效。

回答by Steve Kass

If one of your business requirements isn't well-served by your data model (e.g., you have a requirement to compare dates, but you aren't keeping track of dates, only of date-plus-times), look at the possibility of tuning the model, not the method of coping with it.

如果您的数据模型不能很好地满足您的业务需求之一（例如，您需要比较日期，但您没有跟踪日期，仅跟踪日期加时间），请查看可能性调整模型，而不是处理它的方法。

Would it be possible and helpful to store the date-only in an indexed computed column, or to store the date and time parts separately?

将仅日期存储在索引计算列中，或者单独存储日期和时间部分是否可能并有帮助？

回答by Thomas

Sorry for late answer.

抱歉回复晚了。

i always use this syntax for date comparision

我总是使用这种语法进行日期比较

WHERE CONVERT(VARCHAR(8), date1, 112) = WHERE CONVERT(VARCHAR(8), date2, 112)

it always works great whenever we convert the date with 112 format code it return date in yyyyMMddformat. it compare the date in string format without time but works great. thanks

每当我们用 112 格式代码转换日期时，它总是很好用，它以yyyyMMdd格式返回日期。它比较没有时间的字符串格式的日期，但效果很好。谢谢