MS Access date search missing record

[Scott Selikoff]

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.

[Rob Spoor]

When working with days in any Microsoft database product, I prefer to use DATEDIFF.

[Scott Selikoff]

Thanks Rob, I'll try it out. I do prefer to avoid database functions when possible but pure SQL software systems are hard to construct in the real world.

[Scott Selikoff]

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.

AND DATEDIFF('s',myDateColumn,someValue)>=0
AND myDateColumn <= someValue

[Rob Spoor]

What's the actual value of someValue? Perhaps it's being converted incorrectly when used with <=.

[Scott Selikoff]

Java date with time rounded to nearest second. When I pull the epoch times of both values they are the same.

[Rob Spoor]

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.

[Scott Selikoff]

The value is only off by 1 second not 5 months. The values are entered correctly.

[Rob Spoor]

Let's then just agree that Access is a horrible product I still use it as well for non-production purposes sometimes. In production environment it should be avoided.

[Scott Selikoff]

Or the driver is. I can't help but thinking this is a bug in the driver, but debugging faulty JDBC drivers is unfun.