How to SELECT with date criteria by SQL?

  • Thread starter Thread starter Martin Severin Steffensen
  • Start date Start date
M

Martin Severin Steffensen

I am pretty new to MS Access and I'm experiencing difficulty SELECT'ing data from a table with a criteria expression involving a date. The data in my date&time column is formatted as dd.mm.yyyy hh:mm:ss

My test query is SELECT * FROM blog_entries WHERE created = '08.10.2012 00:00:00' and the error I am receiving is Data type mismatch.

What am I doing wrong?
 
I am pretty new to MS Access and I'm experiencing difficulty SELECT'ing data from a table with a criteria expression involving a date. The data in my date&time column is formatted as dd.mm.yyyy hh:mm:ss

My test query is SELECT * FROM blog_entries WHERE created = '08.10.2012 00:00:00' and the error I am receiving is Data type mismatch.

What am I doing wrong?
Click to expand...

The format of the column is irrelevant: a date/time is stored as a Double
number, a count of days and fractions of a day (e.g. 0.75=6pm) since midnight,
December 30, 1899.

Date literals in a query must be delimited by # rather than by '. I'm not sure
if the period delimiters within the date will work. To avoid possible
ambiguity between American mm/dd/yyyy and the more sensible international
dd/mm/yyyy, it's best to use the unamiguous ISO format (Access, programmed by
Americans, assumes mm/dd/yyyy regardless of system setting!). Try:

#2012-10-08 00:00:00#

Of course if your table field contains a time portion this will not match; if
that's an issue use
= #2012-10-08# AND < #2012-10-09#
Click to expand...
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top