Im using JDBC to connect to a MS Access database. I've tried to execute a query using the 'MONTHS_BETWEEN' function. An SQL exception was thrown saying it didnt like the function. Anyone know how i might be able to run this query through JDBC. I suspect i might need to change to a oracle DB.
JDBC itself doesn't support any SQL functions, or anything else, for that matter. All such support is offered by the underlying database, or the driver for that database. I'm not familiar with this function you mention, but if it's not supported by MS Access, then you won't be able to use it via JDBC connected to MS Access.
Access doesn't support MONTH_BETWEEN. You have to use the dateDiff dunction for Access.
You can refer to this page for differrences between Oracle and Microsoft Access SQL
Joined: Sep 06, 2005
I've tried using DATEDIFF, here is my code
System.out.print("enter date"); String date = input.next(); Statement statement = link.createStatement(); results = statement.executeQuery("SELECT refNo ,DATEDIFF('Month','"+date+"','dateOf') FROM tblFault");
I get a datatype mismatch error. Does the date that the user enters need to be in a date object?
Any help would be much apreciated
Joined: Nov 05, 2004
What's dateOf ? Is that a column in your tblFault table
For the above statement to work, the format of your date should be same as the format of the date in the database. If the format is differrent you will have to use the CONVERT operator to convert string into datetime