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 best way to strip the time portion from a datetime is like this:

dateadd(dd,0, datediff(dd,0, getDate()))

I used to use and advocate a process that looks like this:

cast(floor(cast(getdate() as float)) as datetime)

But I now believe modern versions of sql server optimize the former snippet to be just as fast or faster.

Unfortunately, as fast as that is it's still going to bog down your 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 that it's pre-computed as much as possible. You can accomplish that via a view or computed column.

Finally, note that the DATEDIFF function compares the number of boundaries crossed. So the datediff in days between '2009-09-14 11:59:59' and '2009-09-15 00:00:01' is 1, but the DATEDIFF in days between '2009-09-15 00:00:01' and '2009-09-15 11:59:59' is still zero. Note that it doesn't really care at all about the time portion there. Depending on what your query is trying to do, you might be able to use that to your advantage.

So what you want to do is try both methods and see which works better for your data.

Answer 2

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.

But...it does work.

Answer 3

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?