Compare dates in T-SQL, ignoring the time part

Question

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

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!

Any suggestions?

Edit: 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.

Answer 1

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 Date type, which does not hold a time component, there is little reason to use either of those techniques.

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.

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.

Answer 2

WHERE DATEDIFF(day, date1, date2)=0