oracle周日,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') ;