比较 T-SQL 中的日期，忽略时间部分 [英] Compare dates in T-SQL, ignoring the time part

问题描述

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

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

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

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!

有什么建议吗?

David Andres 的评论:

David Andres' comment:

'比较"包括的不仅仅是平等'

'"comparison" includes much more than equality'

让我意识到我没有把我的问题说得足够清楚 - 我是实际上只是检查相等性，仅此而已.

made me realise that I didn't make my question clear enough - I am actually just checking for equality, that is all.

推荐答案

最合理的方法是去掉日期时间值的时间部分并比较结果，最好的方法是去掉时间部分日期时间是这样的:

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()))

但是现在 Sql Server 具有 Date 类型，它不包含时间组件，几乎没有理由使用这两种技术中的任何一种.

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

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

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.

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

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.

这篇关于比较 T-SQL 中的日期，忽略时间部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！