How to Convert ISO Weeks to Dates in Oracle

http://blog.joachim-selke.de/2012/03/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.
 
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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