How to Convert ISO Weeks to Dates in Oracle

Converting a date to an ISO year-week string is easy in Oracle:

>SELECT TO_CHAR(DATE '2012-03-11', 'IYYY-IW') FROM dual;

2012-10

One should think that converting an ISO year-week string back to a date (denoting the first day in this week) should also be easy:

>SELECT TO_DATE('2012-10', 'IYYY-IW') FROM dual;

ORA-01820: format code cannot appear in date input format
01820. 00000 -  "format code cannot appear in date input format"
*Cause:    
*Action:

So, obviously this is not supported yet (speaking of version 11g R2).

Therefore, I decided to write a conversion function myself. Here it is:

CREATE OR REPLACE FUNCTION iso_week_to_date
  (iso_year IN INTEGER, -- full ISO year, e.g., 2012
   iso_week IN INTEGER) -- ISO week
  RETURN DATE
IS
  jan4_of_iso_year DATE;
  first_day_of_iso_year DATE;
  iso_date DATE;
  iso_date_iso_year INTEGER;
BEGIN
  -- Find the first day of iso_year
  -- (= the Monday of the week containing January 4th)
  jan4_of_iso_year := TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD');
  first_day_of_iso_year := TRUNC(jan4_of_iso_year, 'IW');
  
  -- Add the ISO week (in days)
  iso_date := first_day_of_iso_year + 7 * (iso_week - 1);
  
  -- Check whether iso_week is a valid ISO week
  -- (= whether the Thursday of the week containing iso_date is contained in the year iso_year)
  iso_date_iso_year := TO_CHAR(iso_date, 'IYYY');
  IF iso_date_iso_year <> iso_year THEN
    RAISE VALUE_ERROR;
  END IF;
  
  RETURN iso_date;
END;

A quick test:

SELECT iso_week_to_date(2012, 10) FROM dual;

2012-03-05 00.00.00

Another one:

SELECT iso_week_to_date(2012, 1234) FROM dual;

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JSELKE.ISO_WEEK_TO_DATE", line 23
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

Perfect. 🙂

Have fun!

For those prefering plain SQL:

SELECT TRUNC(TO_DATE(iso_year || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (iso_week - 1) FROM dual;

--Example (iso_year = 2012, iso_week = 10):
SELECT TRUNC(TO_DATE(2012 || '-01-04', 'YYYY-MM-DD'), 'IW') + 7 * (10 - 1) FROM dual;

2012-03-05 00.00.00

UPDATE: Changed TRUNC(..., 'D') to TRUNC(..., 'IW') to remove dependency from the database parameter NLS_TERRITORY.

This entry was posted in Oracle. Bookmark the permalink.

19 Responses to How to Convert ISO Weeks to Dates in Oracle

  1. Nazario says:

    select iso_week_to_date(2012, 1) from dual;

    give me the error:
    “PL/SQL: numeric or value error%s”

    do you why?

    • Works for me (Oracle 11g R2).

      • Nazario says:

        i’am using this version:

        select * from v$version

        Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
        PL/SQL Release 9.2.0.1.0 – Production
        “CORE 9.2.0.1.0 Production”
        TNS for 32-bit Windows: Version 9.2.0.1.0 – Production
        NLSRTL Version 9.2.0.1.0 – Production

    • Michelos says:

      It doesn’t work because the 1st day of 2012 belongs to ISO year of 2011. This code works only if the ISO year matches the actual year.

      • I can’t see any problem here. Please have a look at the following examples:

        SELECT iso_week_to_date(2012, 1) FROM dual;
        2012-01-02 00.00.00
        
        SELECT iso_week_to_date(2011, 52) FROM dual;
        2011-12-26 00.00.00

        Both results are correct, as the first ISO week in 2012 starts on January 2, and the 52nd ISO week in 2011 starts on December 26.

        • Michelos says:

          Maybe we are missing something? I have read that there are various definitions for the ISO week. You code is based on the definition that the first week is the week with that contains the 4th of JANUARY. My example is based on the definition that the first week is the one with the first Thursday of the year.

          If we execute your code on 2009-01 it will respond with 4/1/2009, which means that 4/1/2009 is the first day on the first ISO week of 2009. So, the second day of that week (5/1/2009) would have to be on the same week, right?
          Wrong, because to_char(to_date(’05/01/2009′, ‘DD/MM/YYYY’), ‘IYYY-IW’) return 2009-02.

          Now I am wondering if there is some kind of system/database parameter affecting this behavior. I believe you when you say that your function works on year 2012 and week 1. At my system it does not work and I have the exception that Nazario mentioned.

          Any ideas?

          • I just tried my code, which returns the following for your example (first ISO week of 2009):

            SELECT iso_week_to_date(2009, 1) FROM dual;
            2008-12-29 00.00.00

            And now the “plain SQL” version:

            SELECT TRUNC(TO_DATE(2009 || '-01-04', 'YYYY-MM-DD'), 'D') + 7 * (1 - 1) FROM dual;
            2008-12-29 00.00.00

            Both results are correct, as the first ISO week of 2009 starts on Monday, December 29, 2008.

  2. iersel says:

    i use this function:
    method found on http://stackoverflow.com/questions/506827/how-to-convert-a-week-200851-into-a-date-2008-12-27

    function iyyyiw_todate (p_iyyyiw varchar2)
       return date is
       l_result   date;
    begin
       with param as (select p_iyyyiw as iso from dual)
       select to_date ('04.01.' || substr (iso, 1, 4)) + (to_number (substr (iso, 5, 2))) * 7 - to_char (to_date ('04.01.' || substr (iso, 1, 4)), 'D')  as result
          into l_result
          from param;
       return l_result;
    end iyyyiw_todate;
    

  3. Michelos says:

    Now I really got confused! How this code
    SELECT TRUNC(TO_DATE(2009 || '-01-04', 'YYYY-MM-DD'), 'D') + 7 * (1 - 1) FROM dual;
    can return 2008-12-29?

    You start with the date 2009-01-04 and you add ZERO days to it. 1-1 equals zero, multiplied by 7 gives zero so you add zero days to 2009-01-04!
    I ran your example and, correctly to my opinion, gives… 2009-01-04!

    The same result I receive using iso_week_to_date().

    • I guess we are both right. 🙂

      I get the correct result because of the TRUNC(…, ‘D’) function, which returns the starting day of the week.

      However, as stated here, the behavior of TRUNC(…, ‘D’) is dependent on the initialization parameter NLS_TERRITORY (I didn’t know that).

      I guess I need to change my code so that it does not depend on local settings anymore. Thanks for your feedback!

  4. Pingback: How to Convert ISO Weeks to Dates in Oracle | mkjay

  5. Alexandru Simionov says:

    For me the best way to be sure of the results is to just do it recursively.
    Just run the package bellow, and call the function , it’s pretty self explanatory.

    But here’s also an example for running it after compilation:
    SELECT
    ISO_WEEK_TO_DATE(‘2013_42’,1) FIRST_DAY_OF_WEEK,
    ISO_WEEK_TO_DATE(‘2013_42’,2) SECOND_DAY_OF_WEEK,
    –ETC
    ISO_WEEK_TO_DATE(‘2013_42′,7) LAST_DAY_OF_WEEK
    from DUAL;

    There are also some additional fields mentioned , in case you might need different results.
    But in case you need to change it, please read it and understand how it works beforehand,
    Thanks.

    create or replace
    FUNCTION ISO_WEEK_TO_DATE(ISO_WEEK_IYYY_IW VARCHAR2,WEEKDAY_RETURNED_ISO NUMBER) RETURN DATE
    IS
    RESULT_DATE DATE;
    BEGIN
    — INPUT
    –ISO_WEEK_IYYY_IW; FORMAT IYYY_IW
    –WEEKDAY_RETURNED_ISO; 1-Monday -> 7 Sunday

    SELECT DATE_OF_CHECK INTO RESULT_DATE
    FROM
    (
    SELECT
    SUBSTR(ISO_WEEK_IYYY_IW,1,4) I_YEAR,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’) FIRST_DAY_OF_YEAR,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10 START_DAY_OF_CHECKS,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL DATE_OF_CHECK,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’IYYY_IW’) ISO_WEEK_NO,
    DECODE(TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’D’)-1,0,7,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’D’)-1) ISO_DAY_NR_WEEK,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’Day’) NAME_DAY_WEEK
    FROM DUAL
    WHERE
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’IYYY_IW’)=ISO_WEEK_IYYY_IW
    AND
    DECODE(TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’D’)-1,0,7,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),’YYYY’),’YYYY’)-10+LEVEL,’D’)-1)=WEEKDAY_RETURNED_ISO

    CONNECT BY LEVEL<=400
    );
    RETURN RESULT_DATE;

    END;

    • Alexandru Simionov says:

      Also , just noticed after i copied the function , if you copy/paste you would have to comment again the lines regarding INPUT parameters, sorry about that .
      Cheers.

  6. Alexandru Simionov says:

     create or replace
    FUNCTION ISO_WEEK_TO_DATE(ISO_WEEK_IYYY_IW VARCHAR2,WEEKDAY_RETURNED_ISO NUMBER) RETURN DATE
    IS
    RESULT_DATE DATE;
    BEGIN
    --INPUT
    --ISO_WEEK_IYYY_IW; FORMAT IYYY_IW
    --WEEKDAY_RETURNED_ISO; 1-Monday -> 7 Sunday
    
    SELECT DATE_OF_CHECK INTO RESULT_DATE
    FROM
    (
    SELECT
    SUBSTR(ISO_WEEK_IYYY_IW,1,4) I_YEAR,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY') FIRST_DAY_OF_YEAR,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'’YYYY')-10 START_DAY_OF_CHECKS,
    TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL DATE_OF_CHECK,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'IYYY_IW') ISO_WEEK_NO,
    DECODE(TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'D')-1,0,7,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'D')-1) ISO_DAY_NR_WEEK,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'Day') NAME_DAY_WEEK
    FROM DUAL
    WHERE
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'IYYY_IW')=ISO_WEEK_IYYY_IW
    AND
    DECODE(TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'D')-1,0,7,
    TO_CHAR(TRUNC(TO_DATE(SUBSTR(ISO_WEEK_IYYY_IW,1,4),'YYYY'),'YYYY')-10+LEVEL,'D')-1)=WEEKDAY_RETURNED_ISO
    
    CONNECT BY LEVEL<=400
    );
    RETURN RESULT_DATE;
    
    END;
    

  7. MrEddy says:

    Thanks it helps me

  8. Chri says:

    Thx 🙂

  9. Lars says:

    Thanks. Just what I needed.

  10. Ld says:

    thx good job

Leave a Reply

Your email address will not be published. Required fields are marked *