oracle 年月周日

----年
SELECT TO_CHAR(add_months(to_date('20090101', 'yyyymmdd') , (ROWNUM-1)*12), 'yyyy') statdate
  FROM DUAL
CONNECT BY ROWNUM <= FLOOR(MONTHS_BETWEEN(TO_DATE('20140101', 'yyyymmdd'),
                            TO_DATE('20090101', 'yyyymmdd')) / 12);
                            
---月  
SELECT TO_CHAR(add_months(to_date('200901', 'yyyymm') , (ROWNUM-1)), 'yyyy-mm') statdate
  FROM DUAL
CONNECT BY ROWNUM <= FLOOR(MONTHS_BETWEEN(TO_DATE('200906', 'yyyymm'),
                            TO_DATE('200901', 'yyyymm')) );
---周 
SELECT  '第'||ROWNUM||'周('||to_char(w.ftime,'yyyy-mm-dd')||'至'||to_char(w.etime,'yyyy-mm-dd')||')' AS SHOW,
w.week AS statdate
 FROM (                          
SELECT stat.week ,min(stat.dtime)AS ftime,MAX(stat.dtime) AS etime FROM (                         
SELECT TO_CHAR(TO_DATE('20130901', 'yyyymmdd') + ROWNUM , 'iw') week,
       TO_DATE('20130901', 'yyyymmdd') + ROWNUM AS dtime
  FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('20130930', 'yyyymmdd') -
           TO_DATE('20130901', 'yyyymmdd')
)stat GROUP BY stat.week
ORDER BY stat.week
)W;
                    
--日
SELECT TO_CHAR(to_date('20090901', 'yyyymmdd')+ROWNUM-1, 'yyyymmdd') statdate
  FROM DUAL
CONNECT BY ROWNUM <= TO_DATE('20090930', 'yyyymmdd')-
                            TO_DATE('20090901', 'yyyymmdd') ;