DateAdd for Hours is not always precise

Topic

New Reply

I won’t bore you with the details, but I am able to prove that using the DateAdd() function with the “h” option is not always precise!

You can verify this yourself. In the Immediate Window just enter this:

?DateAdd(“h”,1,#08:00#) = #09:00#

This adds 1 hour to 8:00am and compares the result to 9:00am. As expected, this returns TRUE. But then try this:

?DateAdd(“h”,2,#08:00#) = #10:00#

This returns FALSE!

But wait, it gets better! I had populated a table of TimeSlots using an append query that utilized the DateAdd function. The PrimaryKey was the TimeSlot field, which was a Date/Time field. In looking at the table, there was an entry for 10:00am. I could manually add another record for 10:00am, so that now my table shows 2 records at 10:00am (the Primary Key)! Actually, I even expanded the formatting to show 10:00:00am for both cases.

Just so you won’t think this is insignificant, I discovered this when I wrote a query to return all TimesSlots >=#10:00#. The 10:00am timeslot was NOT returned, it started at 10:15am.

Reply | Quote

Replies

I believe the root cause of this is the way dates are stored in Access. Since you are using hours, that is stored as a fraction in floating point format, and 1 hour rounds up nicely, but 2 hours rounds down when you do it that way, and the last bit is different when you add two hours compared to how it is set if you explicitly set the hour. Makes things a bit challenging for sure.

Reply | Quote

Yes, it is how Dates are stored that is the problem. That’s why I seldom define my numeric fields as singles or doubles; I use currency unless I have a requirement for more than 4 decimal places. I have determined that I can use the TimeSerial() function to form the new time that is compatible with a manual entry. So in my example of adding 2 hours to an existing field (lets call it oldTime), I’d use this instead:

TimerSerial( DatePart(“h”, oldTime) + 2, DatePart(“n”, oldTime), DatePart(“s”, oldTime))

A bit of a pain, but at least I’ll get consistent values.

— added later —

Here is a better way: TimeValue(DateAdd(“h”, 2, oldtime))

Reply | Quote