Noticed an oddity in MS Access using JDBC (sun.jdbc.odbc.JdbcOdbcDriver). I was performing a range query on a date value, aka "SELECT * FROM ... WHERE myDateColumn <= someValue". When myDateColumn and someValue where the same for a particular row, the query failed to return the row. If I add 999 milliseconds to someValue in Java before running the query, the record is returned. If I add 998 milliseconds to someValue in Java before running the query, the record is not returned. This is a PreparedStatement so I'm not converting the date/time directly.
To verify the data in the column, I retrieved all rows in Java and ran getTime() on the myDateColumn values. According to the results, myDateColumn and someValue (without modification) have the exact same epoch value in Java.
Any ideas? I suspect this might be a data error or round off error, but I really hope its not a driver or Access error.
It worked with DATEDIFF, but I'm left to wonder why the following aren't exactly equivalent, when using PreparedStatements for handling date/time conversions considering Access does not support milliseconds.
But in what format is the date? I always prefer to send date literals in "yyyy-MM-dd HH:mm:ss" or "yyyy-MM-dd HH:mm:ss.SSS" format to make sure there's no odd date conversion. Especially dates like 01-06-2012 can cause troubles. On one system it's January 6th, on others it's June 1st.