Discrepancy with time calculations

[Bob Quintal]

=?Utf-8?B?QWRhbSBUaHdhaXRlcw==?=

?Now()-TimeValue("00:30")
39363.7287384259

?Now()+TimeValue("00:30")
08/10/2007 18:29:39

These two formulas give different results in Access 2003, one
being in the correct time format, the other being the numerical
representation of it. Why is this?

ps. I have fixed this using CDate but still wonder why it's
happening it he first place.

Access stores the date/time value in its tables as the number of
days since December 31, 1899, with the time as fraction of a day
using the double precision datatype, and converts it for
display..Sometimes, it forgets to format when you do calculations
directly instead of using the dateadd() and datediff() functions.

 

[Guest]

?Now()-TimeValue("00:30")
39363.7287384259

?Now()+TimeValue("00:30")
08/10/2007 18:29:39

These two formulas give different results in Access 2003, one being in the
correct time format, the other being the numerical representation of it. Why
is this?

ps. I have fixed this using CDate but still wonder why it's happening it he
first place.

 

[John W. Vinson]

?Now()-TimeValue("00:30")
39363.7287384259

?Now()+TimeValue("00:30")
08/10/2007 18:29:39

These two formulas give different results in Access 2003, one being in the
correct time format, the other being the numerical representation of it. Why
is this?

ps. I have fixed this using CDate but still wonder why it's happening it he
first place.

Now() returns a Double Float number, a count of days and fractions of a day
since midnight, December 30, 1899. I'd use DateAdd() to increment date or time
values - if you want a date/time value thirty minutes in the future use

DateAdd("n", 30, Now())

It's "n" for miNutes - "m" is Months.

John W. Vinson [MVP]

 

[Guest]

Adam:

This looks like a case of Access trying to be helpful by second guessing
your intentions when doing simple date/time arithmetic. The assumption seems
to be that when a date/time value is subtracted from another the desired
result is the duration (in days) between the two (the time value #30:00#
really being #12/30/1899 00:30:00# of course), whereas when added the desired
result is a date/time value. I think I can see some slight trace of logic in
this. Its immaterial really, as one would always format the result of any
date time arithmetic.

The fundamental problem here is that a date/time value is being used to
express a duration, which is not what it represents. It actually represents
a point in time. There is actually no such thing in Access as a date value
or a time value, only formatted date/time values. Because of the way in
which date/time values in Access are implemented you can add a date to a time
of less than 24 hours because the underlying floating point number which
represents the latter is less than zero. When its necessary to add a time
duration of 24 hours or greater, however, it falls down as the only way you
could express this as a date/time value, to add 25 hours and 30 minutes to
the current time for instance, would be Now() + #12/31/1899 00:30:00#, which
would be a pretty peculiar way of doing things.

A better approach to date/time arithmetic is to call the DateAdd function:

? DateAdd("n",30,Now())

to add 30 minutes to the current time, or to subtract:

? DateAdd("n",-30,Now())

The interval used should be the smallest to which the duration to be added
or subtracted can be integrally reduced. So for my example above of adding
25 hours and 30 minutes to the current time:

? DateAdd("n",1530,Now())

Incidentally, purely out of interest and not as a suggested solution, your
original example will give a formatted date/time value if the 30 minutes is
expressed as an arithmetical expression:

? Now() - (0.5/24)

Ken Sheridan
Stafford, England