SELECT * FROM ( SELECT CASE WEEKDAY WHEN '1' THEN WEEK + 1 ELSE WEEK END WEEK, WEEKDAY, DY FROM ( SELECT TO_NUMBER(TO_CHAR(DT, 'IW')) WEEK, TO_CHAR(DT, 'D') WEEKDAY, TO_CHAR(DT, 'FMDD') DY FROM (SELECT TO_DATE( TO_CHAR(SYSDATE, 'YY/MM')||'/'|| LPAD(LEVEL,2,0)) DT ,LEVEL DAY FROM DUAL CONNECT BY LEVEL