SQL> CREATE OR REPLACE FUNCTION FN_WEEK2DATE
  2  (p_datestr VARCHAR2 DEFAULT NULL, p_dayinweek NUMBER DEFAULT 1)
  3  RETURN DATE AS
  4    l_datestr VARCHAR2(6);
  5    l_date DATE; l_monday VARCHAR2(255):=TO_CHAR(TO_DATE('20011231','YYYYMMDD'),'DAY');
  6  BEGIN
  7    IF p_datestr IS NULL THEN
  8      l_datestr:=TO_CHAR(SYSDATE,'IWYYYY');
  9    ELSE
 10      IF LENGTH(p_datestr) NOT IN (5,6) THEN
 11        RAISE VALUE_ERROR;
 12      ELSE
 13        l_datestr:=LPAD(p_datestr,6,'0');
 14      END IF;
 15    END IF;
 16    l_date:=NEXT_DAY(TO_DATE('0401'||SUBSTR(l_datestr,3,4),'DDMMYYYY'),l_monday)+(TO_NUMBER(SUBSTR(l_datestr,1,2))-2)*7-(1-NVL(p_dayinweek,1));
 17    RETURN l_date;
 18  END;
 19  /

Funktion er oprettet.

SQL> SELECT TO_CHAR(SYSDATE,'DDMMYYYY') AS today,TO_CHAR(SYSDATE,'IWYYYY') AS thisweek FROM DUAL;

TODAY    THISWE
-------- ------
27102006 432006

SQL> SELECT TO_CHAR(fn_week2date('432006'),'DDMMYYYY') AS firstdaythisweek,
  2  TO_CHAR(fn_week2date('432006',TO_NUMBER(TO_CHAR(SYSDATE,'D'))),'DDMMYYYY') AS today
  3  FROM DUAL;

FIRSTDAY TODAY
-------- --------
23102006 27102006