Oracle custom week of the year numbering based on first Monday of the year I have following sql for generating dates, I wanted to change week of the year numbering based on 'First Monday of the Year'. 

Hence numbering would be, 
01-05-15 ---&gt; 01
01-12-15 ---&gt; 02
01-19-15 ---&gt; 03
...
...
...


SELECT TO_NUMBER (TO_CHAR (CurrDate, 'YYYYMMDD')) AS date_key,
   CurrDate AS alt_full_date_key,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'WW')) AS week_num_of_year,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'IW')) AS week_num_of_year_iso,
   NULL AS week_num_of_year_custom
FROM (    SELECT LEVEL n, TO_DATE ('28/12/2014', 'DD/MM/YYYY') + NUMTODSINTERVAL (LEVEL, 'day') CurrDate
          FROM DUAL
    CONNECT BY LEVEL &lt;= 18000);
ISO Definition is here, You need to check if Jan-01 is Monday, if it is, then you can use ISO week format model 'IW'. If it isn't you can still use that but subtract 1 from that value. 

WITH x AS (SELECT to_char(trunc(SYSDATE,'YYYY'),'DY') y_b FROM dual)
,y AS (SELECT to_date('01-05-15','mm-dd-rr') AS d FROM dual UNION ALL
       SELECT to_date('01-12-15','mm-dd-rr') AS d FROM dual UNION ALL 
       SELECT to_date('01-19-15','mm-dd-rr') AS d FROM dual) 
SELECT y.d your_date
      ,CASE WHEN x.y_b = 'MON' 
            THEN to_number(to_char(y.d,'IW')) 
            ELSE   to_number(to_char(y.d,'IW'))-1 
        END week_num
FROM x,y;


I tested briefly and it looks ok to me, but you should test as well.