[RESOLVED] between dates

auddog

I would like to know if anyone know what the query is for getting information between dates. I would like to include the days that I'm querying. In example if I have 5 items each with a different date (01-01-07, 01-02-07, 01-03-07, 01-04-07, 01-05-07) and I want to query for everything between the date of 01-01-07 and 01-04-07 (including those on 01-04-07) what would the query be. I'm currently using

SELECT * FROM production WHERE date > '2007-01-01' AND date < '2007-01-04';

but that returns between the dates and doesn't include the the last date. Hopefully that makes sense to everyone. I'm using mysql 5. Thanks for any help that you might be able to provide.

Piranha

If the query you have works except returning the first and last date then just change it like this:

SELECT * FROM production WHERE date >= '2007-01-01' AND date <= '2007-01-04';

auddog

Unfortunately, that doesn't give me anything more than the other query. It appears to be the second part of the query ( AND date <= '2007-01-04'; ) that doesn't work. Any other ideas???

Piranha

In what order do you write the date?

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Sxooter

OK, first off, are you using a timestamp or date field? (MySQL calls timestampes datetime btw) If you're storing your date in a varchar / text field, then you need to stop doing that and change the type to date or timestamp.

Then, just use between:

select * from table where datefield between '2006-01-01' and '2006-01-10'

Between is inclusive.

auddog

My field name is date and my datatype is timestamp. I appreciate the replies, unfortunately this is the same result that I have been getting before. I'm still at a lose.

Lars_Berg

SELECT * 
  FROM production 
 WHERE date > '2007-01-01' 
    AND date < '2007-01-04 23:59:59'

If you compare a timestamp with a date, the time component is defaulted to 00:00:00

auddog

That works great - thanks a ton to everyone.