aspose file tools*
The moose likes JDBC and the fly likes retrieving date field from access database using query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "retrieving date field from access database using query" Watch "retrieving date field from access database using query" New topic
Author

retrieving date field from access database using query

siva sankar
Ranch Hand

Joined: Jul 13, 2006
Posts: 68
Hi all,
Am unable to retrieve the users information according to the datewise through the query from access database. The datefield datatype is declared as 'shortdate'. The error is showing as "datetype mismatch criteria".
eg: select name,ddate,department from tablename where ddate between '2006-12-01' and '2006-12-31';
I tried in all date formats like mm/dd/yyyy,dd/mm/yyyy and so on.

Thanks in advance,
Siva
Aravind Prasad
Ranch Hand

Joined: Dec 28, 2005
Posts: 265
Dear siva sankar,
Which database you are using. If it is Oracle then you have to use to_date function to put the search thing in dd/mm/yyyy format

for egs : you have to write the query like this.


If you are using MySQL database instead of to_date you have to use str_to_date function. You will get the answer

About Other databases, i haven't used.

regards

Aravind Prasad
Tony Burleson
Greenhorn

Joined: Dec 31, 2006
Posts: 6
Originally posted by siva sankar:
Am unable to retrieve the users information according to the datewise through the query from access database. The datefield datatype is declared as 'shortdate'. The error is showing as "datetype mismatch criteria".
eg: select name,ddate,department from tablename where ddate between '2006-12-01' and '2006-12-31';
I tried in all date formats like mm/dd/yyyy,dd/mm/yyyy and so on.

Hi Siva,
If you are using Microsoft's Access database, it may have something to do with the syntax of the query itself. If you construct the query as a string (instead of a parameterized query), Access requires a pound sign '#' before and after the date string. I also think Access stores date in the dd/mm/yyyy format (unless changed). Here is a sample from Microsoft's site:
SELECT *
FROM tblInvoices
WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#
Here is a sample used from an access database I quickly made:
SELECT test.invoice
FROM test
WHERE (((test.invoice)<#1/1/2007# And (test.invoice)>=#1/1/2006#));
which gives me all records for 2006.

I hope this helps.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: retrieving date field from access database using query