Jeremy Thompson Jeremy Thompson - 12 days ago 7
SQL Question

Oracle End Of Month values for Date Range

Oracle has a

function:

SELECT LAST_DAY(to_date('01/01/2016','DD/MM/YYYY')) from dual;


Results:

31/01/2016


How can I get a list of the EndOfMonths in a Date Range to use in a WHERE Clause, currently I'm using a big string and it is not intuitive or future proof eg:

SELECT * FROM Balances
WHERE TheDate IN
('31/Jan/2016','29/Feb/2016', '31/Mar/2016','30/Apr/2016', '31/May/2016','30/Jun/2016', '31/Jul/2016','31/Aug/2016',
'30/Sep/2016', '31/Oct/2016','30/Nov/2016')


I'd prefer to use a function to plug in Start and End Dates, any ideas much appreciated.

Answer

Try something like this to generate a last day of month range:

SELECT LAST_DAY(add_months(to_date('01/01/2016','DD/MM/YYYY'), level) )
from dual
CONNECT BY LEVEL <= 10

Change 10 to # months you need. THis assumes "TheDate" is a proper oracle DATE type. Wrap with to_char if needed to format as string.

OK with the Months_Between:

SELECT LAST_DAY(add_months(to_date('01-01-1991','DD/MM/YYYY'), level) )
from dual
CONNECT BY LEVEL <=
(SELECT MONTHS_BETWEEN 
   (TO_DATE('02-02-1999','MM-DD-YYYY'),
    TO_DATE('01-01-1991','MM-DD-YYYY') ) "Months"
    FROM DUAL);