69

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.
Fiona - myaccessible.website
  • 14,481
  • 16
  • 82
  • 117
  • 11
    Don't be concerned with premature optimization: write your query using `DATEDIFF`, then see if it's a bottleneck or not be examining the query plan. If it's a problem, seek an alternative. – Welbog Sep 15 '09 at 14:21
  • Possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) – abatishchev Mar 21 '17 at 17:56

5 Answers5

102

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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 4
    wouldn't that would be less efficient than just using _DATEDIFF(day, date1, date2)_? – KM. Sep 15 '09 at 14:24
  • 2
    I wasn't really looking for a way to strip off the time, but a way to compare two dates ignoring the time. This looks a bit slower to me.. – Fiona - myaccessible.website Sep 15 '09 at 14:31
  • 1
    The "other" method I'm talking about is a computed column that uses the time-stripping code on your datatime column for the computation. That will make sql server do all the work ahead of time, so you can just say 'DateA - DateB' – Joel Coehoorn Sep 15 '09 at 14:42
  • 1
    a persisted computed column will do it ahead of time, a view and a computed column will only do it on demand – KM. Sep 15 '09 at 14:54
  • 1
    You can have "indexed" views as well that do the work up front. – Joel Coehoorn Sep 15 '09 at 15:03
  • 1
    For what it's worth, no discussion on this is complete without mentioning impact of wrapping an indexed column reference inside functions... stopping the condition from being SARGable. I realize you were using GetDate() so not really advocating that, but it's noice if it's mentioned. – ErikE Sep 13 '10 at 04:11
  • The question was specifically for SQL 2005, so the mention of 'date' type is interesting, but only for relevant SQL 2008+ – David Gardiner Jan 30 '15 at 02:57
  • Seems strange to me but might make sense to someone who deals with it more but it only worked for me after also casting the field in the `SELECT` statement. Otherwise, it still compared the time part. – codeMagic Nov 16 '17 at 17:56
42

WHERE DATEDIFF(day, date1, date2)=0

pete
  • 705
  • 6
  • 8
7

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.

David Andres
  • 31,351
  • 7
  • 46
  • 36
4

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?

Steve Kass
  • 7,144
  • 20
  • 26
3

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 yyyyMMdd format. it compare the date in string format without time but works great. thanks

Thomas
  • 33,544
  • 126
  • 357
  • 626
  • Your solution worked on a script I was writing when "IF date_column != GETDATE()" did not. Do you happen to know why that is? – CryptoJones May 19 '15 at 21:32