You can use add_months to increment a date by the number of months. So you just need to calculate the number of months to add.
If you have a row/quarter, cross join this with a three row table. Then to find the start of each month, you:
- Subtract 1 from the quarter, then multiply by 3. Then add the "month within the quarter" - 1
The end date of each is similar, but you don't need to subtract one from the "month within the quarter". Just minus a day from the result of add_months:
with rws as (
select 2017 yr, 1 qtr from dual union all
select 2017 yr, 2 qtr from dual union all
select 2017 yr, 3 qtr from dual union all
select 2017 yr, 4 qtr from dual
), qmths as (
select level-1 q from dual connect by level <= 3
)
select yr, qtr,
add_months(to_date('01-01-' || yr, 'dd-mm-yyyy'), ((qtr-1) * 3) + q ) st,
add_months(to_date('01-01-' || yr, 'dd-mm-yyyy'), ((qtr-1) * 3) + q + 1 )-1 en
from rws
cross join qmths
order by st;
YR QTR ST EN
2,017 1 01-JAN-2017 00:00:00 31-JAN-2017 00:00:00
2,017 1 01-FEB-2017 00:00:00 28-FEB-2017 00:00:00
2,017 1 01-MAR-2017 00:00:00 31-MAR-2017 00:00:00
2,017 2 01-APR-2017 00:00:00 30-APR-2017 00:00:00
2,017 2 01-MAY-2017 00:00:00 31-MAY-2017 00:00:00
2,017 2 01-JUN-2017 00:00:00 30-JUN-2017 00:00:00
2,017 3 01-JUL-2017 00:00:00 31-JUL-2017 00:00:00
2,017 3 01-AUG-2017 00:00:00 31-AUG-2017 00:00:00
2,017 3 01-SEP-2017 00:00:00 30-SEP-2017 00:00:00
2,017 4 01-OCT-2017 00:00:00 31-OCT-2017 00:00:00
2,017 4 01-NOV-2017 00:00:00 30-NOV-2017 00:00:00
2,017 4 01-DEC-2017 00:00:00 31-DEC-2017 00:00:00