Time difference

[John]

Hi

I am trying to find difference between two time values in hours. I have
tried the following?

? dateDiff("h", #23:00:00#, #23:00:00#) gives 0 as expected.

? dateDiff("h", #00:00:00#, #23:00:00#) gives 23??? should it not be 1 as
00:00:00 (12 midnight) is only one hour after 23:00:00 (11 pm)?

? dateDiff("h", #01:00:00#, #23:00:00#) gives 22. Should it not be 2 as
01:00:00 (1 am) is two hour after 23:00:00 (11 pm)?

What is going on? How can I get the desired time difference values?

Thanks

Regards

[Douglas J. Steele]

Re: Time difference

The Date type in Access is intended to store a timestamp: a complete date
and time, not just a time. You need to recognize that a Date field is an 8
byte floating point number, where the integer part represents the date as
the number of days relative to 30 Dec, 1899, and the decimal part represents
the time as a fraction of a day.

When you've got #00:00:00#, Access sees this as 0.0. When you've got
#23:00:00#, Access sees this as 0.9583333. In other words, it sees both
times as being on the 30th of Dec, 1899. Therefore, 23 hours is correct for
the first one, and 22 hours for the second one.

Either include the date in your values, or use the kludge shown in
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web"

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(no e-mails, please!)


"John" <john@nospam.in fovis.co.uk> wrote in message
news:40ba1308$0 $25329$cc9e4d1f @news-text.dial.pipex .com...[color=blue]
> Hi
>
> I am trying to find difference between two time values in hours. I have
> tried the following?
>
> ? dateDiff("h", #23:00:00#, #23:00:00#) gives 0 as expected.
>
> ? dateDiff("h", #00:00:00#, #23:00:00#) gives 23??? should it not be 1 as
> 00:00:00 (12 midnight) is only one hour after 23:00:00 (11 pm)?
>
> ? dateDiff("h", #01:00:00#, #23:00:00#) gives 22. Should it not be 2 as
> 01:00:00 (1 am) is two hour after 23:00:00 (11 pm)?
>
> What is going on? How can I get the desired time difference values?
>
> Thanks
>
> Regards
>
>[/color]

[Bob Quintal]

Re: Time difference

"John" <john@nospam.in fovis.co.uk> wrote in
news:40ba1308$0 $25329$cc9e4d1f @news-text.dial.pipex .com:
[color=blue]
> Hi
>
> I am trying to find difference between two time values in
> hours. I have tried the following?
>
> ? dateDiff("h", #23:00:00#, #23:00:00#) gives 0 as expected.
>
> ? dateDiff("h", #00:00:00#, #23:00:00#) gives 23??? should it
> not be 1 as 00:00:00 (12 midnight) is only one hour after
> 23:00:00 (11 pm)?[/color]

From the helpfile,
If date1 refers to a later point in time than date2, the
DateDiff function returns a negative number.

Since you are not getting a negative, the calculations are
correct. Start at midnight and finish at 11pm is a 23 hour event.

If you want start at 23:00 finish at 00:00, invert the order of
the times. Since Access will assume the same date, it will return
a negative value in the case of 23:00 to 00:00. test if the
interval is negative and add 24 if necessary. Otherwise, you can
include dates in the inputs to the function.


Bob Quintal
[color=blue]
>
> ? dateDiff("h", #01:00:00#, #23:00:00#) gives 22. Should it
> not be 2 as 01:00:00 (1 am) is two hour after 23:00:00 (11
> pm)?
>
> What is going on? How can I get the desired time difference
> values?
>
> Thanks
>
> Regards
>
>[/color]