Oracle by monthly, weekly associated data table

--Each month in the time period, {0}: Start date, {1}: End Date Select To_char (Add_months (To_date ('{0}','yyyy-mm'), ROWNUM-1),                           'yyyymm'As monthlist from DUAL CONNECT by ROWNUM<=Months_between (To_date ('{1}','yyyy-mm'), To_date ('{0}','yyyy-mm')) +1
Example: SELECT to_char (Add_months (to_date ('2017-01','yyyy-mm'), ROWNUM-1),                           'yyyymm'As monthlist from DUAL CONNECT by ROWNUM<=Months_between (To_date ('2017-06','yyyy-mm'), To_date ('2017-01','yyyy-mm')) +1execution Result: monthlist1    2017012    2017023    2017034    2017045    2017056    201706

--weekly within the time period, {0}: Start time, {1}: End Time Select To_char (To_date ('{0}','YYYY-MM-DD') + (ROWNUM-1) *7,                           'Iyyyiw'As weeklist from DUAL CONNECT by ROWNUM<=(TRUNC (to_date ('{1}','YYYY-MM-DD'),'IW') -TRUNC (To_date ('{0}','YYYY-MM-DD'),'IW')) /7+1Example: SELECT to_char (To_date ('2017-04-25','YYYY-MM-DD') + (ROWNUM-1) *7,                           'Iyyyiw'As weeklist from DUAL CONNECT by ROWNUM<=(TRUNC (to_date ('2017-12-01','YYYY-MM-DD'),'IW') -TRUNC (To_date ('2017-04-25','YYYY-MM-DD'),'IW')) /7+1execution Result: weeklist1    2017172    2017183    2017194    2017205    2017216    2017227    2017238    2017249    201725Ten    201726 One    201727 A    201728 -    201729 -    201730 the    201731 -    201732 -    201733 -    201734 +    201735 -    201736 +    201737 A    201738 at    201739 -    201740 -    201741 -    201742 -    201743 -    201744 in    201745 -    201746 to    201747 +    201748

Record complete

Oracle by monthly, weekly associated data table