oracle转换周次,Oracle 動態取周次

--計算當月天數及周次SELECT rn, num_day,       CASE          WHEN MAX (rn)KEEP (DENSE_RANK LAST ORDER BY rn) OVER () = rn             THEN nextday          ELSE nextday - 1       END nextday  FROM (SELECT ROWNUM rn, num_day,LEAD (num_day, 1) OVER (ORDER BY num_day) nextday          FROM (SELECT 1 num_day                  FROM DUAL                UNION                SELECT num_day                  FROM (SELECT ROWNUM num_day,                               TRIM                                  (TO_CHAR (TO_DATE (   '201002'|| TO_CHAR (ROWNUM, '00'),'yyyymmdd'),                                            'day','NLS_DATE_LANGUAGE = english')                                  ) eng_day                          FROM user_objects a                         WHERE ROWNUM <=TO_CHAR (LAST_DAY (TO_DATE ('201002','yyyymm')),'dd')) cal                 WHERE eng_day = 'monday'                UNION ALL                SELECT TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE ('201002','yyyymm')),'dd'))                  FROM DUAL)) WHERE nextday IS NOT NULL