Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Handle ORA-01848: day of year must be between 1 and 365 (366 for leap year) in case condition

user12251389Nov 5 2020 — edited Nov 5 2020

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

This post has been answered by Paulzip on Nov 5 2020
Jump to Answer
Comments
Post Details
Added on Nov 5 2020
13 comments
2,333 views