JDBC supported SQL functions

[jam hall]

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.

Thanks Ahead

[Ernest Friedman-Hill]

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.

[Jayesh Lalwani]

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

[jam hall]

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

Thanks ahead

[Jayesh Lalwani]

What's dateOf ? Is that a column in your tblFault table

try this

SELECT refNo ,DATEDIFF(Month,'"+date+"',dateOf) FROM tblFault

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