DateDiff vs a minus sum

[Evi]

Is there a difference in results between using

DateDiff("d",Date1,Date2)
and
Date2 - Date1?

If yes, any idea why?
Evi

 

[Michel Walsh]

Can, if you cross day 0 :

? CDate(-0.5), CDate(0.5)
12:00:00 12:00:00


-.5 and +.5 represents the same time.



Vanderghast, Access MVP

 

[Douglas J. Steele]

A Date is actually an 8 byte floating point number where the integer portion
represents the date as the number of days relative to 30 Dec, 1899 and the
decimal portion represents the time as a fraction of a day. If Date1 and/or
Date2 contains a time as well as a date, DateDiff will return the number of
days (and note that DateDiff("d", #2008-05-31 23:59:59#, #2008-06-01
00:00:00#) will return 1 day, despite the fact that it's just 1 second!) On
the other hand, Date2 - Date1 will return 1.15740767796524E-05 for those
values.

 

[Michel Walsh]

Another way to see the problem is like this:

The ordered sequence of doubles is like this:
... -11, ... -10.5, ... -10, ... , -1, ... 10, ..., 1000

So, in theory, -10.5 is 'older' than -10.


BUT it is not:

? DateDiff("s", CDate(-10.5), now) > DateDiff("s", CDate(-10), now)
False


so there are less seconds between now and CDate(-10.5) than there are
between now and CDate(-10). Cdate(-10.5) is closer to now than CDate(-10).

An explanation goes like this:


in a date_time value, to the get the date, use the integer part. To get the
time, first, use the absolute value of the number, then, take the decimal
part. DateDiff knows about that way to represent date and time, but
standard arithmetic does not.



Vanderghast, Access MVP

 

[Evi]

Thanks Michel and Douglas.
That fact is well worth remembering, especially if I needed to sum a large
number of days.

Evi

 

[Douglas J. Steele]

"sum a large number of days"? Realistically, you don't do arithmetic with
dates. You might do arithmetic with durations (the number of days between
dates, or the number of seconds between events), but not with the date
fields themselves.