Select query with date condition

Question

I would like to retrieve the records in certain dates after d/mm/yyyy, or after d/mm/yyyy and before d/mm/yyyy, how can I do it ?

SELECT date
FROM table
WHERE date > 1/09/2008;

and

SELECT date
FROM table
WHERE date > 1/09/2008;
AND date < 1/09/2010

It doesn't work.

Answer 1

Be careful, you're unwittingly asking "where the date is greater than one divided by nine, divided by two thousand and eight".

Put # signs around the date, like this #1/09/2008#

Answer 2

The semicolon character is used to terminate the SQL statement.

You can either use # signs around a date value or use Access's (ACE, Jet, whatever) cast to DATETIME function CDATE(). As its name suggests, DATETIME always includes a time element so your literal values should reflect this fact. The ISO date format is understood perfectly by the SQL engine.

Best not to use BETWEEN for DATETIME in Access: it's modelled using a floating point type and anyhow time is a continuum ;)

DATE and TABLE are reserved words in the SQL Standards, ODBC and Jet 4.0 (and probably beyond) so are best avoided for a data element names:

Your predicates suggest open-open representation of periods (where neither its start date or the end date is included in the period), which is arguably the least popular choice. It makes me wonder if you meant to use closed-open representation (where neither its start date is included but the period ends immediately prior to the end date):

SELECT my_date
  FROM MyTable
 WHERE my_date >= #2008-09-01 00:00:00#
       AND my_date < #2010-09-01 00:00:00#;

Alternatively:

SELECT my_date
  FROM MyTable
 WHERE my_date >= CDate('2008-09-01 00:00:00')
       AND my_date < CDate('2010-09-01 00:00:00');