​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama

Generate a list of quarter end dates between two dates

I had a requirement to generate a list of quarter end dates between 2 dates. The first Q end date had to be the quarter before the quarter that the start date was in and the final Q end date had to be the quarter that the end date was in. Here is my solution.


with quarters as

(

    select

       trunc(trunc(to_date('15-jun-12'),'month'),'Q') startdate ,          

       trunc(trunc(to_date('19-jun-15'),'month'),'Q') enddate
    from   dual
)
select add_months(startdate, level*3-3) -1 dt
from   quarters

where add_months(startdate, level*3-3)<=add_months(enddate,3)
connect by level <= months_between(enddate, startdate)

DT
---------
31-MAR-12
30-JUN-12
30-SEP-12
31-DEC-12
31-MAR-13
30-JUN-13
30-SEP-13
31-DEC-13
31-MAR-14
30-JUN-14
30-SEP-14

31-DEC-14
31-MAR-15
30-JUN-15

14 rows selected.