*
The moose likes JDBC and the fly likes MS Access date search missing record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MS Access date search missing record" Watch "MS Access date search missing record" New topic
Author

MS Access date search missing record

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

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


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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.

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

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

Joined: Oct 23, 2005
Posts: 3703
    
    5

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

Joined: Oct 27, 2005
Posts: 19655
    
  18

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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

Joined: Oct 27, 2005
Posts: 19655
    
  18

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Or the driver is. I can't help but thinking this is a bug in the driver, but debugging faulty JDBC drivers is unfun.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MS Access date search missing record
 
Similar Threads
Problem with UNION query
groovy sql confusion
Returning A List Of Variables From A Folder Of Documents And Returning Them Into A New Document
Implementing scanner into a simple login page
Processing with two JComboBox's in one JPanel/JFrame