vba - Bizarre DateAdd behavior -

can explain 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 what: difference has fact binary fractions cannot represent decimal fractions. why floating point offset different when both expressions evaluate 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 sort of floating point artifact in past, it's been because result 2 different types, @ least @ point during evaluation. not seem case here. i'm still confused.

update 2: ross's updated answer provided additional insight problem. i've made progress in tracking down. each answer seems raise new questions. appears both dateadd , date literal using double precision, reason dateadd rounding 18 decimal places (or perhaps truncating @ 19 , not rounding @ 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 might case?

date in vba expressed integer number of days plus floating point fraction representing time. since time float (or perhaps double), cannot express every second perfect precision. 55 seconds 55/86400, or 0.00063657407 of day. not precisely representable in float.

for more insight, try subtracting dateadd value literal value, , converting float.

edit: here's insight talking about:

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

the parsing algorithm takes time literal date structure apparently doing different dateadd function does, leading error in 19th decimal place. guess 1 or other of these using single should using double. can call bug , report microsoft, suppose.

edit 2: google search turned this link people talking floating point reality beneath vba's date type. gave different example, error in 17th place instead of 19th:

? dateadd("h",2,#8:00#) - #10:00# -5.55111512312578e-17  

and there's this gentleman wrote vba code dateadd's job more accurately.