I am trying to extract month from the name field which is working fine till now but suddenly getting error as ORA-01848: day of year must be between 1 and 365 (366 for leap year). The name field is of varchar datatype.
Below is the case condition which is being used to extract month from name field:
CASE
WHEN SUBSTR(NAME, -7, 2) = ' W'
AND regexp_like(NAME, '\d\d$')
THEN TO_CHAR(to_date(TO_CHAR((to_number(SUBSTR(NAME, -5, 2))-1)*7 + DECODE
(TO_CHAR(to_date('01.01.'||20||SUBSTR(NAME, -2),'dd.mm.yyyy'),'D',
'NLS_DATE_LANGUAGE = American'),'1',1,'2',7,'3',6,'4',5,'5',4,'6',3,'7',2,0
)),'DDD', 'NLS_DATE_LANGUAGE = American'), 'MON',
'NLS_DATE_LANGUAGE = American')
END
How do i correct my case condition to resolve the issue and which can also handle dates from next year. Any hint which i can try will be really helpful.
Bellow is the fiddle which is throwing an error for name fields values ending with 'W53-21'. Because its taking the week 53 for year 2021:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e6b2aa0f9de41865152ad9d67c9275dc