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;



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"


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

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

  (iso_year IN INTEGER, -- full ISO year, e.g., 2012
   iso_week IN INTEGER) -- ISO week
  jan4_of_iso_year DATE;
  first_day_of_iso_year DATE;
  iso_date DATE;
  iso_date_iso_year INTEGER;
  -- 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
  RETURN iso_date;


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"


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.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s