Comparing dates in VBA fails - Gotcha

[Minty]

I have two date fields I am comparing to determine if I create a new copy of a local table.
Date Value A is from a linked sql table type datetime , Date Value B is from the local table format date/time (imported from the same table as Value A!).

Value A	                          Value B
05/02/2015 13:22:52              05/02/2015 13:22:52  
42040.5575489236	         42040.5575462963

These are the actual values as access displays / stores them. If you look at them purely as dates ValueA <> ValueB should be correct but it isn't and fails.

I'm sure other people have fallen for this, the only way around it is to format the date values down to Format([ValueA],"yyyy/mm/dd hh:nn:ss") <> Format([ValueB],"yyyy/mm/dd hh:nn:ss") or round them both to the level of precision you require.
(I don't need the additional precision).
I'm assuming this happens when the imported table datetime is truncated locally.
Despite searching I couldn't actually find this anywhere.

 

[CJ_London]

Found this discussion on the subject to back up your point

http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime

 

[Minty]

Interesting link - in fact datetime2 looks very troublesome from an Access point of view. In my case we are still on SQL 2008 so I'm only dealing with the original DateTime format and still developing in AC2003 , I wonder if compatibility things changed in AC2010?

 

[Brianwarnock]

I thought that the way to compare date/time fields was to use date/time functions, namely Datediff and then check the result.

Brian

 

[CJ_London]

Don't know - I use 32bit 2010 and I get this is the immediate window which is the same as your value B

?cdate("05/02/2015 13:22:52")*1
42040.5575462963

and for value A

?format(42040.5575489236,"dd/mm/yyyy hh:nn:ss")
05/02/2015 13:22:52

or if I add another level to seconds - tho' I'm not sure it is valid
?format(42040.5575489236,"dd/mm/yyyy hh:nn:sss")
05/02/2015 13:22:5252
?format(42040.5575462963 ,"dd/mm/yyyy hh:nn:sss")
05/02/2015 13:22:5252

 

[CJ_London]

Good point Brian!

- in the immediate window

?datediff("s",42040.5575462963 ,42040.5575489236)
0

 

[Brianwarnock]

I just remember Pat Hartman mentioning it many years ago, some things stick in the old brain.

Brian

 

[CJ_London]

I guess the next question is 'which is quicker - datediff or text compare?'

 

[Brianwarnock]

My money is on Datediff

Think about what is required

Datediff, convert numbers to the precision required for date/time
Do arithmetic, one function call
Check result

Text compare
Convert numbers to to precision required for date/time
Convert numbers to date/time, two function calls for the Formats
Do compare
Check result

Brian

 

[Minty]

Good points on the differences there Brian and CJ, I'm only comparing about 10 results so the processing time isn't an issue - but worth taking note of...

 

[Galaxiom]

DateDiff every time for me.

This is what happened when I tried arithmetic Time comparisons on SQL server via Access.