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  gt  1 09 2008    and  SELECT date FROM table WHERE date  gt  1 09 2008  AND date  lt  1 09 2010   It doesn t work

User · Accepted Answer

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

User · Answer

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  gt    2008-09-01 00 00 00         AND my date  lt   2010-09-01 00 00 00     Alternatively   SELECT my date   FROM MyTable  WHERE my date  gt   CDate  2008-09-01 00 00 00          AND my date  lt  CDate  2010-09-01 00 00 00

User · Answer

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

User · Answer

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  lt   IN MySQL WITH THIS       select  from  lt   TABLE NAME   gt  where year   COLUMN NAME     gt    DATE   OR YEAR COLUMN NAME   lt    DATE      FOR EXAMPLE   select name  BIRTH from pet1 where year birth  gt  1996 OR YEAR BIRTH  lt  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  lt TABLE NAME  where year COLUMN NAME   1996   FOR EXAMPLE mysql   select name from pet1 where year birth  gt  1996 OR YEAR BIRTH  lt  1989   ----------    name        ----------    bowser       chirpy       whistler    ----------  3 rows in set  0 00 sec

[sql] Select query with date condition

Examples related to sql

Examples related to date

Examples related to ms-access