DateDiff Weirdness

SerranoG · July 14th, 2004, 03:41 PM

I just discovered that DateDiff can give you a wrong answer if you were expecting what I was, so I wanted share it.

If you have Date1 = #12/31/2003# and Date2 = #1/1/2004#, then

Int((Date2 - Date1) / 365.25) = 0

whereas

DateDiff("yyyy", Date1, Date2) = 1

I thought this was a bug because clearly only one day has passed so the DateDiff should return zero also. But in the DateDiff help it says:

Quote:

quote:When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

I also noticed that counting months and weeks ("m" and "ww", respectively) are wrong, too.

This is terrible... DateDiff should return the true number of months and years elapsed, not just subtract the month or year parts from each other!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Steven · July 16th, 2004, 12:38 AM

I came across a similar thing in December:
http://p2p.wrox.com/topic.asp?TOPIC_ID=7622

But I think it makes perfect sense now.
If you want to find the difference in years between two dates, then DateDiff("yyyy", Date1, Date2) is perfect.
If you want to do real calculations with Dates, then I'd just use Date1-Date2 and Multiply/Divide it by however you want it formatted.

Steven

I am a loud man with a very large hat. This means I am in charge