ORACLE CONVERT CHAR FORMAT “YYYY-WW” TO DATE FORMAT

I am trying to convert char date format “YYYY-WW” in ORACLE to date for calculating by week in year but have a trouble with error message format code cannot appear in date input format

If i write

TO_DATE(TO_CHAR(TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH:MI:SS'),'YYYY-MM'),'YYYY-MM')

It will be normal

But if i write

TO_DATE(TO_CHAR(TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH:MI:SS'),'YYYY-WW'),'YYYY-WW')

The message error format code cannot appear in date input format appear. I don’t have no idea how to convert it right

Thanks for any advice

Answer

You can use:

TO_DATE( year || '-01-01', 'YYYY-MM-DD' ) + INTERVAL '7' DAY * (week - 1)

For your code:

SELECT TO_DATE( TO_CHAR(dt, 'YYYY') || '-01-01', 'YYYY-MM-DD' )
         + INTERVAL '7' DAY * (TO_CHAR(dt, 'WW') - 1) AS week_start
FROM   (
  SELECT TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH24:MI:SS') AS dt
  FROM   DUAL
);

---

If you want to use ISO weeks (which always start on a Monday, rather than counting from the 1st January as WW does) then:

TRUNC( TO_DATE( iso_year || '-01-04', 'YYYY-MM-DD' ), 'IW')
  + INTERVAL '7' DAY * (iso_week - 1)

db<>fiddle here