Link to home
Start Free TrialLog in
Avatar of mbunkows

asked on

JDBC: Selecting Dates

I'm having problems using date ranges as a selection criteria.

I'm communicating with access via the JDBC-ODBC bridge.

The sample SQL statement is:
SELECT otherCol, dateCol FROM table WHERE dateCol = '01/01/99'
I'm actually selecting a date range using something like:
SELECT otherCol, dateCol FROM table WHERE dateCol BETWEEN '01/01/99' AND '12/31/99'
but the first example doesnt work either (and its the simplest case).  
Ive tried formating the date any number of ways. (19990101, 1/1/99, 01/01/1999, etc)
The column in the Access database is a Date/Time (with no time).  An example field is displayed in Access as 1/1/99.

Error Message:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Statement stmt = con.createStatement();
String queryString= "SELECT col1, dateCol FROM table WHERE dateCol = '01/01/99'";
ResultSet rs= stmt.executeQuery(queryString);

What is really interesting is Ive tried using:
SELECT otherCol, dateCol FROM table WHERE dateCol = 01/01/99
(no single quotes)
and I don't get any error messages.  However, I dont get any records either.  Ive looked at a number of SQL resources and they all say to put dates in single quotes.  Does the driver somehow think that the date column is a Number instead of Date/Time?
Is there anything Im doing wrong?
Avatar of Jim Cakalic
Jim Cakalic
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mbunkows


Comment accepted as answer
Works good.

Its strange that all the online references I've found use ' to wrap dates (like strings).

Is there an online version of the reference you mentioned or any other good reference?

If you have a URL handy that would be great so I don't have to bother you guys with these slightly offtopic postings.

If not thats ok.  You answered my question.