当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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.

网友答案:

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#

网友答案:

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'); 
网友答案:
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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: