WITH t AS ( SELECT TO_DATE('200812', 'yyyymm') + LEVEL - 1 dt FROM dual CONNECT BY LEVEL <= 100 ) SELECT dt , TO_CHAR(TRUNC(dt, 'iw'), 'yyyy') y , 'W' || LPAD( ( TRUNC(dt, 'iw') - NEXT_DAY(TRUNC(TRUNC(dt, 'iw'), 'yy') - 1, 2) ) / 7 + 1, 2, '0') || CASE WHEN TO_CHAR(TRUNC(dt, 'iw'), 'mm') != TO_CHAR(TRUNC(dt, 'iw') + 6, 'mm') THEN DECODE(TO_CHAR(TRUNC(dt, 'iw'), 'mm'), TO_CHAR(dt, 'mm'), 'A', 'B') END w FROM t ;