MySQL Query for certain date range -

i'm having following table data:

table: seasons                 id          --------------------------- 1    2013-08-30  2013-09-04 2    2013-09-05  2013-09-08 3    2013-09-09  2013-09-20 

i need run query returns records within date range, example: return records affected 2013-09-04 2013-09-05

it like

date  range:                    | 09-04 - 09-05|  seasons:          08-30 - 09-04 | 09-05 - 09-08     | 09-09 - 09-20 

so should return first 2 records. i've tried query between seams need build several cases - or there simpler way? thankx

it's amazing no 1 has noticed 2 years, the other answers wrong because didn't take account case when both start date , end date fall beyond scope of search range. consider range of date:

start_date <<---------------------------- date range --------------------------->> end_date 

and range of our search:

start_date <<---------------------------- date range --------------------------->> end_date                   start_search <<-------- search range -------->> end_search 

the search should give positive result because intersect. if use other answers, negative result because neither start_date nor end_date between start_search , end_search.

to solution, let's draw 4 possible modes of intersection:

                  start_date <<---------- date range --------------------------->> end_date  start_search <<------------------------- search range -------->> end_search

start_date <<---------------------------- date range ---------->> end_date                 start_search <<---------- search range ------------------------>> end_search

start_date <<---------------------------- date range --------------------------->> end_date                   start_search <<-------- search range -------->> end_search

                 start_date <<----------- date range -------->> end_date  start_search <<------------------------- search range ------------------------>> end_search

you can or 4 possible cases obtain straightforward solution:

select*from table     /* 1st case */ start_date between start_search , end_search          or /* 2nd case */  end_date  between start_search , end_search          or /* 3rd case */ (start_date <= start_search , end_date >= end_search) or /* 4th case */ (start_date >= start_search , end_date <= end_search)  /* 4th case here redundant since being covered 1st , 2nd cases */ 

a less straightforward solution is:

select*from table      start_date  between start_search , end_search /* covers 1st , 4th cases */           or start_search between  start_date  ,  end_date  /* covers 2nd , 3rd cases */

try visualize using diagrams above.

---

if attempt extrapolate pattern out of 4 diagrams above, can see during intersection, end_date >= start_search, , on flip side, start_date <= end_search. indeed, visualizing further, can see when 2 conditions hold, cannot not have intersection.

as such, solution simple as:

select*from table  end_date >= start_search && start_date <= end_search 

and advantage of solution need 2 comparisons. contrast "or everything" approach requires 2 as 8 (3 + 3 + 2) comparisons. (each between call consists of 3 comparisons.)