CODEWITHSUNDEEP
javajdbcms-access-2007
Dnyaneshwar
Dnyaneshwar

Reputation: 99

MS Access retrieve data between two dates using JDBC

I want to retrieve data between two dates from MS Access using JDBC.

I have tried

String query= "SELECT lastlogin FROM loginHistory " +
            "WHERE lastlogin BETWEEN #01/07/2013# AND #03/07/2013#"+
            "ORDER BY lastLogin DESC";

I am not getting the desired results. Whats the problem. please help me out !

Thank You.

Upvotes: 0

Views: 2489

Answers (4)

Dnyaneshwar
Dnyaneshwar

Reputation: 99

got the solution by transforming the date format from 01/07/2013 to 2013/07/01

String query= "SELECT lastlogin FROM loginHistory " +
            "WHERE lastlogin BETWEEN #2013/07/01# AND #2013/07/03#"+
            "ORDER BY lastLogin DESC";

Thank You all for supporting me..

Upvotes: 2

AllTooSir
AllTooSir

Reputation: 49402

I am not getting the desired results.

I believe then your code compiles but you are not getting the resultset what you expect. Although your code posted over here misses a quotation mark. I believe that it is a typo , or else the code wouldn't have compiled itself.

Check if the date string in the query is in correct format . Execute the query as a PreparedStatement :

String query= "SELECT lastlogin FROM loginHistory " +
        "WHERE lastlogin BETWEEN ? AND ?"+
        "ORDER BY lastLogin DESC";

And set the date strings after formatting it properly using Date format. Also check what is the datatype for your column , is it VARCHAR/String or DATE etc. You can even use the format() in the sql query itself :

Select lastlogin From loginHistory where format(lastlogin,"dd/mm/yyyy")
BETWEEN  format(#01/07/2013#,"dd/mm/yyyy") AND 
format(#03/07/2013#,"dd/mm/yyyy") ORDER BY lastLogin DESC;

Upvotes: 0

Kon
Kon

Reputation: 10810

Your SQL syntax is correct, but you have an extra quotation mark in the middle of your code, directly after the first date in the BETWEEN clause.

Upvotes: 0

Suresh Atta
Suresh Atta

Reputation: 122006

Query missed the " in the end and you added in the middle.

Try 

String query= "SELECT lastlogin FROM loginHistory " +
            "WHERE lastlogin BETWEEN #01/07/2013# AND #03/07/2013# "+
            "ORDER BY lastLogin DESC";

Upvotes: 0

Related Questions