Oracle: ISO Week, wrong answer

In Oracle 11 and Oracle 12, issuing the following select:

SELECT TO_CHAR((TO_DATE('19720101','YYYYMMDD')), 'YYYYIW') || ' ' || TO_CHAR((TO_DATE('19721230','YYYYMMDD')), 'YYYYIW') AS ERRATO FROM DUAL;

I get:

197252 197252

I expected something like:

197152 197252 or 197153 197252

Why do I get the same result 197252 197252?

You're currently formatting with 'YYYYIW' which is the 4 digit year, followed by the ISO week-of-year. I suspect you want the ISO week-year, followed by the ISO week-of-year.

The Oracle documentation is very unclear on this, but you should try 'IYYYIW' as the format string instead. That uses the "4-digit year based on the ISO standard" (IYYY).

(I'm trying to run that on sqlfiddle to see if it works, but it's hung at the moment...)

See more on this question at Stackoverflow