Bizarre DateAdd behavior

Question

Can anyone explain the following results:

?DateAdd("s", 54, 0) = #12:00:54 AM#
True

?DateAdd("s", 55, 0) = #12:00:55 AM#
False

?DateAdd("s", 56, 0) = #12:00:56 AM#
True

UPDATE: Ross Presser's answer provides the what: the difference has to do with the fact that binary fractions cannot always represent decimal fractions. But WHY is the floating point offset different when both expressions evaluate to the same data type?

?TypeName(DateAdd("s", 55, 0))
Date

?TypeName(#12:00:55 AM#)
Date

?VarType(DateAdd("s", 55, 0)) = VarType(#12:00:55 AM#)
True

When I've encountered this sort of floating point artifact in the past, it's usually been because the result was actually two different types, at least at some point during the evaluation. That does not seem to be the case here. I'm still confused.

UPDATE 2: Ross's updated answer provided additional insight into the problem. I've made progress in tracking this down. Each answer seems to raise new questions. It appears that both DateAdd and the date literal are using double precision, but for some reason DateAdd is rounding to 18 decimal places (or perhaps truncating at 19 and not rounding at all):

?CDbl(#12:00:55 AM#) - CDbl(55/86400)
 0 

?CDbl(DateAdd("s", 55, 0)) - CDbl(55/86400)
-1.0842021724855E-19 

?0.000636574074074074 - 0.0006365740740740741
-1.0842021724855E-19 

Any ideas why this might be the case?

Answer 1

Date in VBA is expressed as an integer number of days plus a floating point fraction representing the time. Since the time is a float (or perhaps a double), it cannot exactly express every second with perfect precision. 55 seconds is 55/86400, or 0.00063657407 of a day. This is probably not precisely representable in a float.

For more insight, try subtracting the Dateadd value from the literal value, and converting to float.

EDIT: Here's the insight I was talking about:

? cdbl(dateadd("s",55,0)) - cdbl(#12:00:55 AM#)
-1.0842021724855E-19 

The parsing algorithm that takes the time literal to a Date structure is apparently doing something different than the dateadd function does, leading to an error in the 19th decimal place. My guess would be that one or the other of these is using Single where it should be using Double. You can call this a bug and report it to Microsoft, I suppose.

EDIT 2: A google search turned up this link where people are talking about the floating point reality beneath VBA's date type. They gave a different example, where the error is in the 17th place instead of the 19th:

? DateAdd("h",2,#8:00#) - #10:00#
-5.55111512312578E-17 

And there's also this gentleman who wrote some VBA code to do DateAdd's job more accurately. (The site on that page presents the code in a badly formatted code block that destroys all the newlines, but the code is downloadable.)