Search code examples
sqloracle-databaseplsqldate-range

Get a list of month last days between 2 dates?

As the title states, I am trying to get the list of month last days between 2 dates.

Example:

  • Date 1 -> 2014-08-15
  • Date 2 -> 2014-10-15

Expected output

2014-08-31
2014-09-30

The query I am using to achieve this is the following:

SELECT DISTINCT LAST_DAY(TO_DATE('2014-08-15') - 1 + ROWNUM)
FROM ALL_OBJECTS
WHERE TO_DATE('2014-08-15') - 1 + ROWNUM <= TO_DATE('2014-10-15');

However I have a feeling there might be a more elegant way to achieve it.

Any input would be appreciated!

Solution

  • To get the same result as yours:

    select add_months(LAST_DAY(date'2014-08-15'),level-1)
from dual 
connect by add_months(trunc(date'2014-08-15','mm'),level-1)<= date'2014-10-15';

    But I think 2014-10-31 doesn't satisfy your condition - "between 2 dates".

    So probably it should be something like

    select add_months(LAST_DAY(date'2014-08-15'),level-1)
from dual 
connect by add_months(LAST_DAY(date'2014-08-15'),level-1)<=date'2014-10-15';

    or

    select add_months(LAST_DAY(date'2014-08-15'),level-1)
from dual 
connect by level<=months_between(date'2014-10-15',date'2014-08-15');

    Final query:

    select add_months(LAST_DAY(date'2014-11-07'),level-1)
from dual where add_months(LAST_DAY(date'2014-11-07'),level-1)<=date'2014-12-07'
connect by add_months(LAST_DAY(date'2014-11-07'),level-1)<=date'2014-12-07';