Month and day comparison in Oracle

datetime, oracle, sql

Solution

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');

Problem

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.

Original source