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
x
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
Advertisement
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_startFROM ( 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