Parse an ISO 8601 week string (YYYY-Www or YYYY-Www-D) to an Oracle DATE value. Oracle's TO_*datetime functions do not support ISO year 'IYYY' Or ISO week 'IW' format model elements.

parse_isoweek.plsql

CREATE OR REPLACE FUNCTION parse_isoweek(isostring_in STRING) RETURN DATE 
IS 
  l_year_start DATE;
  l_year VARCHAR2(4);
  l_week VARCHAR2(2);
  l_day VARCHAR2(1);
  l_date DATE;

  --c_pattern allows for 'YYYY-Www' and 'YYYY-Www-d' formats.
  c_pattern CONSTANT VARCHAR2(33) := '^(\d{4})-W([0-5]\d)((-)([1-7]))?$';
  
BEGIN
  l_year := REGEXP_SUBSTR(isostring_in,c_pattern,1,1,'c',1);
  
  --if l_year is null, then we have ill-formed input
  IF l_year IS NULL THEN 
    RAISE VALUE_ERROR;
  END IF;
  
  l_week := TO_NUMBER(REGEXP_SUBSTR(isostring_in,c_pattern,1,1,'c',2));
  l_year_start := trunc(TO_DATE(l_year || '-01-04','YYYY-MM-DD'),'IYYY');
  l_date := l_year_start + 7*(l_week-1);
  if l_year <> TO_CHAR(l_date, 'IYYY') THEN 
    RAISE VALUE_ERROR;
  END IF;

  l_day := REGEXP_SUBSTR(isostring_in,c_pattern,1,1,'c',5);
  --we accept both YYYY-Www and YYYY-Www-d formats
  if l_day IS NOT NULL THEN
     l_date := l_date + TO_NUMBER(l_day)-1;
  END IF;

  RETURN l_date;  
END;