List:General Discussion« Previous MessageNext Message »
From:Miguel Cardenas Date:June 24 2007 12:13am
Subject:Selecting rows by DATE ranges
View as plain text  
Hello list

I found a little problem with an application am developing, in particular 
creating reports by DATE ranges.

Examples:

select ... where date>"2007-01-01";
returns all records where date is greater (and equal inclusive) to 2007-01-01

select ... where date>="2007-01-01";
returns all records where date is greater/equal to 2007-01-01

the ">" and ">=" have the same effect

select ... where date>"2007-01-01" and date<"2007-01-20";
returns all records where date is greater/equal to 2007-01-01 and less 
than "2007-01-20"
*** DOES NOT RETURN RECORDS FROM DAY *20*

select ... where date>"2007-01-01" and date<="2007-01-20";
returns all records where date is greater/equal to 2007-01-01 and less 
than "2007-01-20" although I'm using "<="
*** DOES NOT RETURN RECORDS FROM DAY *20*

My doubts are:

1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the 
specified date, I mean apply a strict GREATHER THAN

2. how can I retrieve rows with date<="..." INCLUDING the day of the specified 
date. currently I have to do a date>="date1" and date<="date2+1day"

I need to retrieve rows in this way

date>X
date>=X
date<X
date<=X
date>X and date<Y
date>=X and date<Y
date>=X and date<=Y
date>X and date<=Y

and so... didn't find a function to specify ranges of dates and the LESS 
THAN/EQUAL operator does not include the last day, so my reports with "<=" 
are done by adding one day but don't like to use it this way since it could 
be confusing and generate errors on reports.

Thanks for any comment,
Miguel
Thread
Problem with GRANT ... 'user'@'%'Miguel Cardenas20 May
  • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz20 May
    • Re: Problem with GRANT ... 'user'@'%'Mathieu Bruneau20 May
      • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz20 May
        • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May
        • Selecting rows by DATE rangesMiguel Cardenas24 Jun
          • Re: Selecting rows by DATE rangesPintér Tibor24 Jun
          • Re: Selecting rows by DATE rangesMogens Melander25 Jun
            • Re: Selecting rows by DATE rangesJørn Dahl-Stamnes25 Jun
      • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May
        • Re: Problem with GRANT ... 'user'@'%'Mogens Melander22 May
    • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas21 May
      • Re: Problem with GRANT ... 'user'@'%'Mogens Melander21 May
      • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz21 May
        • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May