Today I was asked how to convert iyyy and iw values into dates, in particular how to convert the day-of-week value since there is no “id” format mask. As it turns out, it wouldn’t matter if there was because the “i” format masks are not supported in TO_datetime functions. They can only be used in TO_CHAR conversions from datetime values.
The week-number calendar described in the ISO standard starts on the first Monday prior to the first Thursday that occurs within the Gregorian calendar. Furthermore the standard defines two formats for representing dates in the ISO week-numbering system. An extended form yyyy-Www-d and a compact form yyyyWwwd
Where W is the literal W letter, ww are the ISO weeks 1-53, d is the day of the ISO week 1-7 for Monday through Friday, and of course, yyyy the ISO year which mostly corresponds with the Gregorian calendar year except for the first few and last few days of each year which could fall into a different ISO year than the Gregorian year.
The first tricky part of this problem was finding the first Thursday. I couldn’t use day-of-week numbers because those vary by NLS_TERRITORY and I couldn’t use names because those vary by NLS_LANGUAGE. The easiest solution to this I could think of was to use a date that I already knew to be a Thursday and then extract the day name for that date. Doing this means I will always be able to refer to the “Thursday” day by the correct name, regardless of the current session’s NLS_LANGUAGE setting. It’s a tiny bit of overhead but neatly solves the problem.
Next was validating the inputs to make sure they follow one of the two standard formats. A regular expression nicely covers both. Then a few substring extractions pull out the year, week and day numbers. Again, I validate the values so users can’t pass in week 0, 86, or other out-of-range value.
The math to construct a date from the ISO values is then fairly straight forward. Start with Thursday, then find Monday, then increment for weeks and days to get the final result.
One last check ensures that a 53 week input applied to a 52 week year will also be rejected.
The final function using the above logic ended up looking like this:
CREATE OR REPLACE FUNCTION isoweekdate(p_date IN VARCHAR2)
RETURN DATE
IS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
v_result DATE;
v_week PLS_INTEGER;
v_day PLS_INTEGER;
-- Get the weekday name of a known Thursday. Doing it this way
-- ensures the function will work regardless of the NLS_LANGUAGE setting
--- 1971 is first year recognizing this format, so use its first Thursday
v_nls_thursday VARCHAR2(3) := TO_CHAR(DATE '1971-1-7', 'DY');
BEGIN
--- Assert the input string is an ISO8601 week date string in Extended or Compact from
--- Extended: yyyy-Www-d
--- Compact: yyyyWwwd
--- where "W" is the literal W character, ww is the ISO week number
IF NOT REGEXP_LIKE(p_date, '^[0-9]{4}-W[0-9]{2}-[1-7]$|^[0-9]{4}W[0-9]{2}[1-7]$')
THEN
raise_application_error(-20001, 'Invalid format, must be yyyy-Www-d or yyyyWwwd');
END IF;
v_week := TO_NUMBER(SUBSTR(p_date, INSTR(p_date, 'W') + 1, 2), '99');
IF v_week NOT BETWEEN 1 AND 53
THEN
raise_application_error(-20002, 'Illegal week number, must be between 1 and 53');
END IF;
v_day := TO_NUMBER(SUBSTR(p_date, -1), '9');
v_result :=
NEXT_DAY(TRUNC(TO_DATE(SUBSTR(p_date, 1, 4), 'yyyy'), 'yyyy') - 1, v_nls_thursday) -- find first Thursday in the Gregorian calendar year
- 4 -- Go back four days adding the first day of the ISO year will fall on Monday
+ ((v_week - 1) * 7) -- Increment for the number of weeks
+ v_day; -- Increment for the day number, 1=Monday, 7= Sunday
-- If someone tries to use Week 53 in a 52-week year
-- the result will improperly shift into the following year.
-- Check for that condition and raise exception if it happens
IF v_week = 53 AND TO_CHAR(v_result, 'iyyy') > SUBSTR(p_date, 1, 4)
THEN
raise_application_error(-20003, 'Illegal week 53 in a 52 week year');
END IF;
RETURN v_result;
END;
Some sample usage:
SQL> select isoweekdate('2004-W53-6') from dual;
ISOWEEKDATE('2004-W53-6')
2005-01-01
SQL> select isoweekdate('2006-W01-1') from dual;
ISOWEEKDATE('2006-W01-1')
2006-01-02
SQL> select isoweekdate('2019-W03-4') from dual;
ISOWEEKDATE('2019-W03-4')
2019-01-17
SQL> select isoweekdate('2019W041') from dual;
ISOWEEKDATE('2019W041')
2019-01-21
SQL> select isoweekdate('2019W042') from dual;
ISOWEEKDATE('2019W042')
2019-01-22
This should work well for my friend, hopefully it will help others as well.