is there a way to get date from yyyywwd format in Oracle PLSQL?

Question

2020425 means 2020 year 42nd week and 5th day of that week, which is October 16th 2020. I need some function to do this .

Answer 1

ISO weeks start on Mondays, so the 42nd week of 2020 has its first day on Monday 12 October 2020. That would make the fifth day Friday 16 October, not Saturday 17 October.

Here is some expression that evaluates to 16 October 2020.

The logic is:

• 4 January is always in the first ISO week.
• So the next Monday, after that date, is the first day of the second ISO week
• We can add the number of given weeks to that, minus 2 weeks, counting 7 days for each week
• Finally add the day number, minus one, as we already were at the first day of the week

The input to this SQL is provided in the sub query:

select next_day(to_date(substr(fmt, 1, 4) || '0104', 'YYYYMMDD'), 'MONDAY')
     + (to_number(substr(fmt, 5, length(fmt)-5))-2)*7
     + to_number(substr(fmt, -1)) - 1
from (select '2020425' as fmt from dual);

Output:

16-OCT-20