Thread: Date comparison without a year

Hello
There is a table with user reviews about hotels, every row has columns: time_of_travel_begin and time_of_travel_end. User can select date range (interval), for example 15 June - 10 July and DB request should return all reviews for any years (for 15 June - 10 July 2010, 15 June - 10 July 2009 etc). For now I've constructed this request:

SELECT *
  FROM comments
  WHERE
          hotel_id = 4323
          AND
          (
            TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
            OR
            TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD') BETWEEN TO_DATE('06-15', 'MM-DD') AND TO_DATE('07-10', 'MM-DD')
            OR
            (TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') < TO_DATE('06-15', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD') > TO_DATE('07-10', 'MM-DD'))
          )

and this select works.

But if first date of interval is in December and the second date of interval is in January (for example interval 20 December - 10 January) - it's a problem. Select will be looks like:

SELECT *
  FROM comments
  WHERE
          hotel_id = 4323
          AND
          (
            TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
            OR
            TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD') BETWEEN TO_DATE('12-20', 'MM-DD') AND TO_DATE('01-10', 'MM-DD')
            OR
            (TO_DATE(TO_CHAR(time_of_travel_begin, 'MM-DD'), 'MM-DD') < TO_DATE('12-20', 'MM-DD') AND TO_DATE(TO_CHAR(time_of_travel_end, 'MM-DD'), 'MM-DD') > TO_DATE('01-10', 'MM-DD'))
          )

this select returns wrong results.

How can I retrieve all reviews from 20 Dec to 10 Jan for any year?

Thanks in advance