Forum Stats

  • 3,734,282 Users
  • 2,246,937 Discussions


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

user12251389 Member Posts: 306 Blue Ribbon
edited Nov 5, 2020 2:30PM in SQL & PL/SQL

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:

                    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),''),'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')

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:


Best Answers


  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    The NAME field is in string data type, and it is not a "date" in any standard date format, so it's not clear what you mean by extracting month from it.

    If you backtrack through your sample code, you will find that at an intermediate stage (before you apply the second-to-last function call, TO_DATE), some of the values are 367. WHY that is is a different question; but once you got those values, TO_DATE with the 'DDD' format model WILL throw that error.

    So, why do you get those values? I don't know. I could figure it out, perhaps, but I don't see how that would help. You didn't explain the problem to us. Apparently something like W53-21 is supposed to encode a date (although I will take the chance to guess "week" rather than "date", and if you want to extract month from a week, that will be an issue) - but I don't like to work based on guesses.

    If those are indeed encodings for "weeks", how are "weeks" defined? When do they start? Are those ISO weeks? (I assume not; if they were, then you could use more standard date functions, not the manipulations in your code. But who knows.) And, given any week, what do you mean by "extracting month from it"? Obviously weeks may straddle two months, so you need to be more specific.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    I already checked the dbfiddle (otherwise how would I know that some of your input values result in the number 367, which then causes the error you saw?)

    I understand "month" as in 'DEC' etc. The issue is where you extract it from. Extract month from a single date: easy. Date is 11 October 2018, then the month is October. But what do you mean by "extract month from a week" (assuming W53-21 really means "week", which you still didn't confirm)? And what does W53-21 mean anyway?

    For example, for Europeans like me, a week begins on a Monday and it ends on a Sunday. The week that began Monday, Sept. 28 2020 ended on Sunday, Oct.4 2020. So, what "month" are you extracting from this WEEK? Are you extracting the month from THE FIRST DAY of the week? From the LAST DAY of the week? Or from the THURSDAY of the week? Or what else? (And this still doesn't tell me what W53-21 means, or whether it begins on a Monday or on some other day of the week.)

    Please answer the questions in my first Reply - you didn't answer any of them. Until I understand the problem in all its details, this will be my last contribution in this thread. Good luck!

  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Nov 5, 2020 6:04PM

    @Paulzip thanks very much this works fine and expected...just one update i want result like below with only month name. Can you please tell how to get that:

      Name                                                              Output  
      XXX Peak Weekly W47-20                          NOV   
      NOVYYY WEEKLY Fut W51-20                   DEC    

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    I am confused.

    I asked the OP to clarify some things. He did not. You posted a solution; I don't see where you explained what assumptions you made. What is a "week"? What is the month "of a week"?

    If I read your code correctly, you determine the "day of the year" by multiplying the week number by 7. Which means that the first week is from January 1 to January 7, regardless of what day of the week January 1 is. That is already an unusual definition; I assume perhaps the OP was doing something like that in his code, and you just followed his lead?

    But then, after some manipulations, you truncate to the beginning of the ISO week. That is really odd. One definition in converting from week number to day-of-the-year, but then truncate to the beginning of the week according to a different definition? Note also there is no reference to ISO anything in the OP's question or any of his replies.

    You also made the guess (without stating it explicitly - even though I asked the OP about this twice) that the "month of a week" means the month of the first day in the week. (Or - of the first day in the ISO week that contains the date you computed using a different definition of week...)

    The OP already accepted your answer as the correct answer, so somehow you must have gotten it right. I just don't understand how.

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    OP actually posted a link to a website with an online weekly calendar that clarified things in an unambiguous manner, but it looks like he's removed that post.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    I missed that. But even so, what I pointed out still doesn't make sense. In the computation, converting from week to day-of-the-year uses one definition of "week", but later you truncate to ISO week (which is different from the first definition). Was that also part of the unambiguously clarified things?

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond
    edited Nov 7, 2020 2:47PM

    It appears you are correct. I cobbled that solution together quickly and tested it for a range of years (I picked 20-21) and it seemed to work, but I checked it again with more years and it doesn't work for 2019.

    next_day(to_date(week_num*7||'/'||year default null on conversion error, 'ddd/rr') - 8, 'mon')

    Might be a better approach, but obviously it suffers from the next_day + NLS_DATE_LANGUAGE issue.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    I don't see what that new formula is implementing either. If I am reading it correctly, it assumes that "weeks in a year" are defined so that week 1 is always from Jan 1 to Jan 7 (regardless of what day of the week Jan 1 is), it computes the last date of the input week, and then it finds the Monday within the week, and then it takes the month of that Monday?

    Assuming that's what the formula does - is THAT the OP's definition of "week of the month"? (Even assuming that the definition of "week" is the one in the OP's business problem?) How do we know that?

    This is why I wanted the OP to answer my questions. I now suspect that he doesn't understand them, so I won't be able to help him.

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    No, the second attempt was a formula to give the start date for an ISO week number and year combo.

    OP posted a weblink to a week based calendar, which appeared to be ISO weeks (number weeks, starting from Monday, some week 1 were not 1st of Jan).

    He then asked how do I get the 3 letter month name from that, which was to_char(<dt>, 'MON')

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    OK - then the attempt fails.

    You are still using week_num * 7 to give you the 'ddd' element. There are years where the ISO week is 53. For week 53, the computation 53*7 has the result 371. There is no year with ddd = 371.

    For example, try your formula on week 53 of (ISO) year 2004, which is a valid week. Your formula will default to NULL on conversion error, even though the input week is valid.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown
    edited Nov 7, 2020 5:41PM


    The formula is incorrect more generally, not just for week 53. Namely, I believe it is incorrect exactly when the ISO week begins in the prior calendar year (not ISO year!)

    For example, your (most recent) computation for the input "week 2 of year 2003" returns the date of Monday, January 13, 2003. That is the second Monday in the calendar year; but it is the beginning of ISO week 3, not 2.

    Incidentally, I believe the simplest and most efficient computation of "the Monday of iso week WW in iso year YYYY" is the one that follows the definition exactly, step by step. ISO week 1 of ISO year YYYY is defined by taking the magic date of January 4 in CALENDAR year YYYY and truncating to the "ISO week", meaning - truncating to Monday. Regardless of whether that Monday still falls in CALENDAR year YYYY or the preceding CALENDAR year, that Monday is always the first Monday of ISO week 01 in ISO year YYYY. Then, to extend this to any ISO week in the same year, just add 7 * (week number MINUS ONE). In pseudo-code:

    Monday of ISO week WW in ISO year YYYY =  trunc(to-date('YYYY' || '-01-04', 'yyyy-mm-dd'), 'iw') + 7 * (WW - 1)

    This is only about finding the Monday of the ISO week, given the ISO week number and the ISO year number. I am still not sure why the "month of week 1 in 2003" should be DECEMBER (obviously, of 2002 - but the OP seems to ignore that). This is strictly about how the OP uses "month of a given week" in his business use case; something he has not explained anywhere.

Sign In or Register to comment.