CODEWITHSUNDEEP
sqldatems-access
help_pls
help_pls

Reputation: 143

Select query with date condition

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.

Upvotes: 14

Views: 169874

Answers (4)

MIDHUN H
MIDHUN H

Reputation: 1

hey guys i think what you are looking for is this one using select command. With this you can specify a RANGE GREATER THAN(>) OR LESSER THAN(<) IN MySQL WITH THIS:::::

select* from <**TABLE NAME**> where year(**COLUMN NAME**) > **DATE** OR YEAR(COLUMN NAME )< **DATE**;

FOR EXAMPLE:

select name, BIRTH from pet1 where year(birth)> 1996 OR YEAR(BIRTH)< 1989;
+----------+------------+
| name     | BIRTH      |
+----------+------------+
| bowser   | 1979-09-11 |
| chirpy   | 1998-09-11 |
| whistler | 1999-09-09 |
+----------+------------+

FOR SIMPLE RANGE LIKE USE ONLY GREATER THAN / LESSER THAN

mysql> select COLUMN NAME from <TABLE NAME> where year(COLUMN NAME)> 1996;

FOR EXAMPLE mysql>

select name from pet1 where year(birth)> 1996 OR YEAR(BIRTH)< 1989;
+----------+
| name     |
+----------+
| bowser   |
| chirpy   |
| whistler |
+----------+
3 rows in set (0.00 sec)

Upvotes: -1

dnyaneshwar
dnyaneshwar

Reputation: 11

select Qty, vajan, Rate,Amt,nhamali,ncommission,ntolai from SalesDtl,SalesMSt where SalesDtl.PurEntryNo=1 and SalesMST.SaleDate=  (22/03/2014) and SalesMST.SaleNo= SalesDtl.SaleNo;

That should work.

Upvotes: 1

smirkingman
smirkingman

Reputation: 6358

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#

Upvotes: 34

onedaywhen
onedaywhen

Reputation: 57023

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');

Upvotes: 15

Related Questions