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.

Granth

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#

Chair

Nice to see people once again putting -1 on the correct answer, and without adding a comment to explain why – Chair

-1. This way of doing it relies on the default date format being dd/mm/yyyy. The other answer uses the ISO date format. The OP did ask "how do I do it" not just "why is my sql returning the wrong answer" – Trothplight

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

Screwworm

I don't understand why you'd recommend against BETWEEN with dates. Yes, you have to recognize that your results may not be as expected if you don't account for the time component, but that's not a flaw with BETWEEN, but simple pilot error from not understand the storage of your data. – Sip

@David-W-Fenton: The floating point nature of the storage (of which this pilot is fully aware) means that the only way of using BETWEEN with Access (ACE, Jet, whatever) is to always round the values to your desired level of accuracy (one day, one second, etc but sub-second accuracy is tricky...) but users are using NOW() in their queries so you have to revoke privileges from the base tables and expose the required functionality using stored procs with DATETIME parameters that you can round... it all gets to be too much effort. – Screwworm

@David-W-Fenton: "Yes, you have to recognize that your results may not be as expected" -- all the applications I support require that all results be as expected. Don't yours...? – Screwworm

How can anyone be responsible for expectations born out of ignorance? The problem is not with BETWEEN, but that people very often do not understand how dates are stored. They therefore make mistakes. This is not a flaw in BETWEEN, but a plain old mistake that is caused by ignorance. Recommending that people avoid BETWEEN because of potential mistakes born out of ignorance implies that you don't think it would be better for them to rectify their lack of understanding. – Sip

@David-W-Fenton: Ignorance is built in by design: we call it 'privileges'. The non-admin user will be ignorant of some base tables; rather, they will have to work with 'helper VIEW s and PROCEDURE s. Temporal databases are non-intuitive e.g. a sequenced update in a valid-state model requires five SQL-92 statements (two INSERT s and three UPDATE s) -- I had to look that up! If I can't remember it, I can't expect users to. Encapsulating this complexity is a good thing, IMO. Ignorance is bliss, if you like. – Screwworm

@David-W-Fenton: For the record, I don't think there is a flaw with BETWEEN in Access (ACE, Jet, whatever). It's one of the (few?) SQL-92 features that is implemented perfectly in Access. – Screwworm

@David-W-Fenton: Here's a question that may illustrate my point: the predicate date_param BETWEEN start_date AND end_date -- relies on both start_date AND end_date being included in the period. Let's say the period in question is 'the year 2010' and the DBMS is Access (ACE, Jet, whatever): what is the value of end_date? If the DBMS was SQL Server and the data type was DATETIME (other temporal types are available, unlike in Access) I could say for for certain it would be 2010-12-31 23:59:59.997. – Screwworm

@David-W-Fenton: ...it's tempting to thin that it is one second before midnight because Access's temporal functionality (DATEADD, DATEDIFF, etc)'s smallest time granule is one second but the floating point nature allows subsecond values without mapping them to time granules (e.g. decimal seconds, milliseconds, etc) e.g. SELECT CDATE('2010-01-01 00:00:00') AS start_date, CDATE('2010-12-31 23:59:59') AS end_date, CDATE('40543.99999') AS date_param, CBOOL(date_param BETWEEN start_date AND end_date) AS result; returns FALSE but instead using 40543.99998 returns TRUE. – Screwworm

@Sip ...now I happen to think that if you are only allowed one temporal data type then FLOAT is perhaps a good choice. After all, time is endlessly divisible and the smallest time granule is infinitesimally small. In Access using BETWEEN with a period delimited using a pair of DATE time values is analogous to finding the largest FLOAT value less than 40544. But why settle for a DBMS that has such limitations? – Screwworm

I still don't get it. Who is recommending 2010-12-31 23:59:59.997 as your terminator for the BETWEEN operation? If you want it to be inclusive of all dates in 2010, you wouldn't use BETWEEN, seems to me. That is, assuming you have a time component in your data (which I try to avoid myself). For instance, it would be nonsensical, I think, for invoice dates to have a time component. But an awful lot of Access apps I've seen use Now() instead of Date() for populating such fields. Every time I take over one of them, I have to truncate the date values to integer values. – Sip

@David-W-Fenton: I think you'll see that you've answered you own question when I correct your misstatement: "assuming you have a time component in your data" -- the DATETIME type in Access always has a time element (the clue's in the name). Your database may be designed to use whole days as the smallest time granule but how to you stop users from using sub-day values in queries? As I said earlier, removing privileges from base tables and creating 'helper' procs is a lot of hard work just to be able to use DATETIME as if it were DATE. – Screwworm

You know, you are just impossible to have a discussion with. You know exactly what I mean by saying "assuming you have a time component in your data" -- it obviously means (and you know it means) "a time MEANINGFUL component, as opposed to all values being whole numbers (i.e., date only)". I just don't understand the objection. My users aren't writing queries -- they hired me to develop an Access application for them so THEY DON'T HAVE TO WRITE QUERIES. Your objections are incoherent and nonsensical, and I'm done here, as you aren't adding a damned thing to the discussion. – Sip

@David-W-Fenton: I can't teach you new tricks in SO comments. One of the (if not THE) most important books on the subject is 'Developing Time-Oriented Database Applications in SQL' by Richard Snograss. It is available for free in PDF format (cs.arizona.edu/people/rts/tdbbook.pdf). Start at chapter 4 (p90) and read through to the end of chapter 7 (p216). – Screwworm

Answer 3

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.

Dicarlo

hmmm ... the edit changed the comment below the code from "not working" to "work" - quite the opposite, isn't it? So now, what's was your intention, answer (as now) or question (as before the edit)? – Mattress

Answer 4

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)

Frontier