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.