Original Task: Determine month in MM format given an ISO week. Simple, right? Well, it is not so straightforward, as one cannot use the WW or IW date formats in an Oracle TO_CHAR statement.
Trying to get the Month of a week – say, the Month of an ISO week (those are less obvious) in Oracle SQL or PL/SQL?
Imagine something like this:
select to_char( to_date( week, 'WW'),'Month') from table_with_week_column;
We all use TO_CHAR and date formats to turn date parts into character pieces, like YYYY for ‘2024’ and ‘DD’ for ’02’ and ‘MON’ for ‘JUN’ but did you know you cannot use ‘WW’ or ‘IW’ to get the week or ISO weeek?
Well, you can’t. You get this error:
ORA-01820: format code cannot appear in date input formatWhich isn’t immediatley obvious.
One has to do things like this:
to_char(trunc(TO_DATE(b.year,'YYYY'),'IYYY')+ b.wk * 7,'Month') month,
to_char(trunc(TO_DATE(b.year,'YYYY'),'IYYY')+ b.wk * 7,'MM') mm,I had to search a bit for an answer. When I first encountered this, I found help here – thank you @oraclenerd:
http://www.oraclenerd.com/2010/04/ora-01820-format-code-cannot-appear-in.html
The Oracle SQL reference to DateTime Format Models is at the bottom of the page here:
I figured the world needs another reminder of this out there, hopefully this helps!