ORACLE CONVERT CHAR FORMAT "YYYY-WW" TO DATE FORMAT-CodePudding

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

CodePudding user response：

You can have a look at this for guidance. https://asktom.oracle.com/pls/apex/asktom.search?tag=week-of-year-in-sql-confusing

CodePudding user response：

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

CodePudding user response：

There isn't a built-in way to convert a week number back to a date, as the result would be a bit arbitrary - which of the (up to) seven days in the week should it return?

If you want the first day of the week, as would be found by truncating the original date to WW precision (which is based on the day of the week of the first day of the year - docs), then you can reverse that process by getting the first day of the year and then adding the number of days in the specified number of weeks.

To do that you need to split your string into the two component parts:

substr('1970-01', 1, 4)
substr('1970-01', 6, 2)

and convert the first part to a date:

to_date(substr('1970-01', 1, 4) || '-01-01', 'YYYY-MM-DD')

and the second part to a number of weeks:

to_number(substr('1970-01', 6, 2))

then subtract one from that number of weeks, multiply by seven, and add it to the base date:

select to_date(substr('1970-01', 1, 4) || '-01-01', 'YYYY-MM-DD')
    (7 * (to_number(substr('1970-01', 6, 2)) - 1))
from dual

1970-01-01

It's bit hard to tell that has been adjusted, so trying with today's date, which is week 30 of 2022:

select to_date(substr('2022-30', 1, 4) || '-01-01', 'YYYY-MM-DD')
    (7 * (to_number(substr('2022-30', 6, 2)) - 1))
from dual

2022-07-23

which matches what trunc(sysdate, 'WW') gives.

db<>fiddle

If you want a specific day of the week then you'll need to figure out how much to adjust that, based on what day of the week the first day of the year was.

CodePudding user response：

Select Extract (year from TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH:MI:SS')) || '-' || TO_CHAR(TO_DATE('1970-01-01 07:00:00', 'YYYY-MM-DD HH:MI:SS'),'WW') From dual