Re: problem for selecting range of date between 2 dates

[irek kordirko]

create table dates
SELECT year( dta ) year,
       month( dta ) mon
FROM (
    select adddate( '2000-01-01', interval (@x:=@x+1) month ) dta
    from information_schema.columns, ( select (@x:=-1) x ) x
) xx;

select * from dates;

+------+------+
| year | mon  |
+------+------+
| 2000 |    1 |
| 2000 |    2 |
| 2000 |    3 |
| 2000 |    4 |
| 2000 |    5 |
| 2000 |    6 |
| 2000 |    7 |
| 2000 |    8 |
| 2000 |    9 |
| 2000 |   10 |
| 2000 |   11 |
| 2000 |   12 |
| 2001 |    1 |
| 2001 |    2 |
| 2001 |    3 |
| 2001 |    4 |
| 2001 |    5 |
| 2001 |    6 |
| 2001 |    7 |
| 2001 |    8 |
...
...
...
...
| 2168 |   12 |
| 2169 |    1 |
| 2169 |    2 |
| 2169 |    3 |
| 2169 |    4 |
| 2169 |    5 |
| 2169 |    6 |
| 2169 |    7 |
| 2169 |    8 |
| 2169 |    9 |
| 2169 |   10 |
| 2169 |   11 |
| 2169 |   12 |
| 2170 |    1 |
| 2170 |    2 |
| 2170 |    3 |
| 2170 |    4 |
| 2170 |    5 |
| 2170 |    6 |
| 2170 |    7 |
+------+------+


set @start_year = 2008;
set @end_year = 2011;
set @start_mon = 7;
set @end_mon = 2;

select * from dates
where ( year >= @start_year and mon >= @start_mon OR year > @start_year )
      AND
      ( year <= @end_year and mon <= @end_mon OR year < @end_year )
;

+------+------+
| year | mon  |
+------+------+
| 2008 |    7 |
| 2008 |    8 |
| 2008 |    9 |
| 2008 |   10 |
| 2008 |   11 |
| 2008 |   12 |
| 2009 |    1 |
| 2009 |    2 |
| 2009 |    3 |
| 2009 |    4 |
| 2009 |    5 |
| 2009 |    6 |
| 2009 |    7 |
| 2009 |    8 |
| 2009 |    9 |
| 2009 |   10 |
| 2009 |   11 |
| 2009 |   12 |
| 2010 |    1 |
| 2010 |    2 |
| 2010 |    3 |
| 2010 |    4 |
| 2010 |    5 |
| 2010 |    6 |
| 2010 |    7 |
| 2010 |    8 |
| 2010 |    9 |
| 2010 |   10 |
| 2010 |   11 |
| 2010 |   12 |
| 2011 |    1 |
| 2011 |    2 |
+------+------+


set @start_year = 2009;
set @end_year = 2009;
set @start_mon = 2;
set @end_mon = 5;

select * from dates
where ( year >= @start_year and mon >= @start_mon OR year > @start_year )
      AND
      ( year <= @end_year and mon <= @end_mon OR year < @end_year )
;

+------+------+
| year | mon  |
+------+------+
| 2009 |    2 |
| 2009 |    3 |
| 2009 |    4 |
| 2009 |    5 |
+------+------+


set @start_year = 2009;
set @end_year = 2009;
set @start_mon = 3;
set @end_mon = 3;

select * from dates
where ( year >= @start_year and mon >= @start_mon OR year > @start_year )
      AND
      ( year <= @end_year and mon <= @end_mon OR year < @end_year )
;

+------+------+
| year | mon  |
+------+------+
| 2009 |    3 |
+------+------+