Having trouble using Dates in SQL

[Corey McGlone]

Let me try to describe my situation. I need to perform an update on a record in an Access database and I need to pick which record to update by two criteria, one of which is a Date field (in Access, the actual datatype of the field is Date/Time).
Here is what I currently have:

// Get a DB connection from the DriverManager
Connection dbConnect = DriverManager.getConnection("jdbc:odbc:MyODBCSource", "", "");
Date rightNow = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a");
String reqDate = sdf.format(req.getRequestTime());
String completedDate = sdf.format(rightNow);

// Here, I create my SQL Statement
Statement sqlStmt = dbConnect.createStatement();
String sql = 
"UPDATE Requests SET "
+ "requestCompleted = '" + completedDate 
+ "' WHERE contractNum = '" + req.getContractNumber() 
+ "' AND requestReceived = '" + reqDate + "'";

numRowsEffected = sqlStmt.executeUpdate(sql);

However, whenever I try to execute that code, I get the following exception:

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

So does anyone know how I can get around this? I need to use that date/time as the criteria for the record I'm after (because contractNum might not be unique but contractNum + requestReceived will be) but I can't seem to get that to work. Any ideas?
Thanks, folks.
Corey
[ April 22, 2004: Message edited by: Corey McGlone ]

[Mark Spritzler]

Yes, I do.
You see Access and its Date/Time datatype is a pain in the arse. I had this identical problem with ODBC and VB 6.0
here is my solution. In the query you need to format the Date/Time field into a String and then you can have it in your WHERE clause, as in
Format$(NetWorths.EntryDate,'MM/DD/YYYY')
So instead of NetWorths.EntryDate, you will put in your actual table.fieldname. This will format the date/time into just the date as a string
So in your WHERE clause put something like
WHERE
Format$(NetWorths.EntryDate,'MM/DD/YYYY') = '04/22/2004'
Good Luck
Mark

[Bear Bibeault]

I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues.

[Eric Pascarello]

My 2 cents is that you should try using a Date instead of a String, but I do not know Java!

[Mark Spritzler]

So your code re-written would be

 
// Get a DB connection from the DriverManagerConnection 
dbConnect = DriverManager.getConnection("jdbc:odbc:MyODBCSource", "", "");
Date rightNow = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("mm/dd/yyyy");
String reqDate = sdf.format(req.getRequestTime());
String completedDate = sdf.format(rightNow);
// Here, I create my SQL
StatementStatement sqlStmt = dbConnect.createStatement();
String sql = "UPDATE Requests SET "
+ "requestCompleted = '" + completedDate 
+ "' WHERE contractNum = '" + req.getContractNumber() 
+ "' AND FORMAT$(requestReceived,'MM/DD/YYYY') = '" + reqDate + "'";
numRowsEffected = sqlStmt.executeUpdate(sql);
 

Hope that helps.
Mark
[ April 22, 2004: Message edited by: Mark Spritzler ]

[Bear Bibeault]

Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver do the work (and do it properly) for you?
[ April 22, 2004: Message edited by: Bear Bibeault ]

[Mark Spritzler]

Originally posted by Bear Bibeault:
Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver so the work (and do it properly) for you?

Unfortunately the problem isn't with Java, it is with Access and how to do queries against its tables and a Date/Time datatype.
Mark

[Corey McGlone]

Originally posted by Bear Bibeault:
I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues.
I should have pointed this out originally, I suppose, but I had actually already tried that. I was hoping to do just what you were thinking - avoid the issue, entirely. Unfortunately, when I tried that, I got this:

String sql = 
    "UPDATE Requests SET "
    + "requestCompleted = '" + completedDate 
    + "' WHERE contractNum = '" + req.getContractNumber() 
    + "' AND requestReceived = ?";

PreparedStatement sqlStmt = dbConnect.prepareStatement(sql);
sqlStmt.setTimestamp(1, new Timestamp(new Date().getTime()));

numRowsEffected = sqlStmt.executeUpdate(sql);
 
// During Execution, I catch the following exception:
 
java.sql.SQLException: Driver does not support this function

Anyway, I'm going to try Mark's suggestion and see if I can't get past my error. Thanks for the help, everyone.
Corey

[Eric Pascarello]

The reason I said it is that I passing a string to my stored proceedure with .NET and a SQL Server 2000 database and it was erroring out saying that the data types were a mis-match so I forced it to be a date on the .NET side and it cleared my error.
Man that is one long sentence there...

[Corey McGlone]

Well, Mark's suggestion got me a little further, but it's still not quite working. I'm not getting the error message now, so it appears that it is successfully comparing the Dates now. Unfortunately, I can't get it to match the record I want in the database to perform the update. The extra crummy part is that now I don't get an error message, but I also can't really see what's going wrong, either - it just says that there were no rows to update by returning 0 from the executeUpdate method.
One thing I probably should have explicity pointed out earlier is that the database is storing a Date as well as a Time. For example, an entry in the database might look like this:
2/19/2004 11:36:29 AM
I tried taking Mark's suggestion a little further (along with the MS Access Help files) and came up with this line:

String sql = "UPDATE Requests SET "
        + "requestCompleted = '" + completedDate 
        + "' WHERE contractNum = '" + req.getContractNumber() 
        + "' AND FORMAT$(requestReceived,'m/d/yyyy h:mm:nn AM/PM') = '" 
        + reqDate + "'";

Unfortunately, I still can't get it to match the record I want. The worst part about it is that I don't know what the "FORMAT$" function is really doing to my String to make it match the date in Access. Is there any way that I could see the result of that function? Maybe I can do a little VBA script in Access to pop that up in a message box or something... I'll try that out tomorrow when I'm back at work.
Anyway, I just thought I'd let you know where I sit right now. If I can't get this figured out soon, I might just turn those Date/Time fields into String fields - I'm just not getting much benefit from using the specialized datatype and it's giving me a big headache.
Thanks for the help so far, folks,
Corey
[ April 22, 2004: Message edited by: Corey McGlone ]

[Mark Spritzler]

If you intend to use the Time portion in your query, you will find it very very difficult to get a match. I suggest using just the date portion, unless there will be two records for the same ID and date, then you will have to use the Time, but maybe not so much of a precision.
The query has to be precise, or you will be off. Even for a second.
Mark
[ April 22, 2004: Message edited by: Mark Spritzler ]

[Eddie Vanda]

I read somewhere that dates in access are stored as floating point numbers with the whole number being the date and the fraction being the time. A fraction of .5 would be noon.
A property of fractions, based on binary bits, is that they cannot always represent every possible decimal number so rather than matching for equality you should match against an earlier and later date/time to be sure to get your match.
I now use integer numbers to represent dates: eg 20040423 for today and that seems to work ok for me. I store time in a separate minute of the day field also as an integer.
If your db is already set up you should think about range checking to get your date.