ORA-01820: format code cannot appear in date input format

I learned something new today...you can't use week date format qualifiers in TO_DATE.

I was trying to transform a week to a month like this:

CJUSTICE@TESTING>SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL;
SELECT TO_DATE( 200803, 'YYYYWW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Then tried one "W"

  1* SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Then "IW"

  1* SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
CJUSTICE@TESTING>/
SELECT TO_DATE( 200803, 'YYYYIW' ) FROM DUAL
                        *
ERROR at line 1:
ORA-01820: format code cannot appear in date input format

Datetime Format models in 10gR2

Lo and behold the Error messages manual didn't have it (at least where I would expect it).



Put it up on twitter and Gary Myers responded:



That's good to know.

Now I just need to figure out how to transform that week into a month.

I'm trying to generate some test data for a quick...test.

I switched to using SYSDATE as that would allow more more flexibility to transform the other way.

SELECT DISTINCT
  TO_NUMBER( TO_CHAR( r, 'YYYYWW' ) ) fiscal_year_week,
  TO_NUMBER( TO_CHAR( r, 'YYYYMM' ) ) fiscal_year_month
FROM 
  (
    SELECT SYSDATE - rownum r
    FROM dual
      CONNECT BY LEVEL <= SYSDATE - TRUNC( SYSDATE, 'YEAR' )
  )

Which gave me exactly what I needed

FISCAL_YEAR_WEEK FISCAL_YEAR_MONTH
---------------- -----------------
          201015            201004
          201014            201004
          201012            201003
          201011            201003
          201007            201002
          201006            201002
          201002            201001
          201016            201004
          201010            201003
          201003            201001
          201017            201004
          201013            201003
          201009            201003
          201009            201002
          201005            201002
          201004            201001
          201013            201004
          201008            201002
          201005            201001
          201001            201001