• 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

retrieving date field from access database using query

 
Ranch Hand
Posts: 68
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 265
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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