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