Month and day comparison in Oracle

Question

How do you compare month and day together in datetime operations in Oracle?

Assume, currentdate as = 15 Aug 2014 (in datetime format).

I have to compare currentdate (excluding year) with 2 different static dates excluding year, e.g.

Filter as - 15/Aug between 01/Jul and 20/Dec.

I think for the above to work we need to extract day and month together in datetime format. Please suggest answers. Thanks.

Solution 1

You can use the TO_CHAR function to convert the date to text and then compare.

SELECT *
FROM mytable
WHERE TO_CHAR(SYSDATE, 'MMDD') BETWEEN TO_CHAR(start_date, 'MMDD') AND TO_CHAR(end_date, 'MMDD');

The BETWEEN operator is inclusive of the limits. If you do not want to include the limits, you can use:

SELECT *
FROM mytable
WHERE TO_CHAR(SYSDATE, 'MMDD') > TO_CHAR(start_date, 'MMDD') 
AND TO_CHAR(SYSDATE, 'MMDD') < TO_CHAR(end_date, 'MMDD');

Solution 2

Simply use to_char to get month and day (both as two-digit values) concatenated, then use between:

select *
from mytable
where to_char(mydate,'mmdd') between '0815' and '1220';