• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC supported SQL functions

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author and iconoclast
Posts: 24207
46
Mac OS X Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 502
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What's dateOf ? Is that a column in your tblFault table

try this


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
 
reply
    Bookmark Topic Watch Topic
  • New Topic