By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,485 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

Time difference

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <jo**@nospam.infovis.co.uk> wrote in message
news:40***********************@news-text.dial.pipex.com...
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

Nov 13 '05 #2

P: n/a
"John" <jo**@nospam.infovis.co.uk> wrote in
news:40***********************@news-text.dial.pipex.com:
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)?
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

? 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


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.