10 Replies Latest reply: Jun 22, 2007 8:48 AM by Rob van Wijk RSS

    ORA-01846: not a valid day of the week

    Patza
      Hi,

      I'm getting this the following error when the procedure containing the query :

      SELECT NEXT_DAY(p_curr_date,'SATURDAY') INTO l_last_day FROM DUAL;

      is called from front end (JDBC). The stub of this procedure runs successfully in the sqlplus environment.

      ORA-01846: not a valid day of the week

      I even tried changing the following parameter:

      ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';
      ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

      May I know why this happens ?
        • 1. Re: ORA-01846: not a valid day of the week
          438877
          What is the type of p_curr_date column in your table ?

          Rgds.
          • 2. Re: ORA-01846: not a valid day of the week
            Patza
            p_curr_date IS IN parameter of DATE type which comes in format 'YYYY-MM-DD'
            • 3. Re: ORA-01846: not a valid day of the week
              438877
              p_curr_date IS IN parameter of DATE type which comes in format 'YYYY-MM-DD'
              DATE values are kept in Oracle in the inner numeric form and don't have "format".
              You mean what you pass DATE value as STRING. This is the problem.
              You have to use to_date() explicit conversion:
              SQL> select next_day('23/06/2007','SATURDAY') from dual;
              select next_day('23/06/2007','SATURDAY') from dual
                              *
              ERROR at line 1:
              ORA-01843: not a valid month


              SQL> select next_day(to_date('23/06/2007','DD/MM/YYYY'),'SATURDAY') from dual;

              NEXT_DAY(
              ---------
              30-JUN-07
              Rgds.
              • 4. Re: ORA-01846: not a valid day of the week
                Rob van Wijk
                SQL> select next_day('23/06/2007','SATURDAY') from dual
                  2  /
                select next_day('23/06/2007','SATURDAY') from dual
                                             *
                FOUT in regel 1:
                .ORA-01846: not a valid day of the week


                SQL> select next_day(to_date('23/06/2007','DD/MM/YYYY'),'SATURDAY') from dual
                  2  /
                select next_day(to_date('23/06/2007','DD/MM/YYYY'),'SATURDAY') from dual
                                                                   *
                FOUT in regel 1:
                .ORA-01846: not a valid day of the week


                SQL> alter session set nls_date_language = 'american'
                  2  /

                Sessie is gewijzigd.

                SQL> select next_day(to_date('23/06/2007','DD/MM/YYYY'),'SATURDAY') from dual
                  2  /

                NEXT_DAY(TO_DATE('2
                -------------------
                30-06-2007 00:00:00

                1 rij is geselecteerd.
                Regards,
                Rob.
                • 5. Re: ORA-01846: not a valid day of the week
                  Patza
                  Thanks a lot guys for your quick response... the problem was with the language ... But it was not resolving even after changing the nls_date_language to 'AMERIAN' or 'ENGLISH' ... the following article helped me to resolve the bug.

                  http://www.oracle.com/technology/oramag/code/tips2004/tip9.html

                  Regards
                  Pat
                  • 6. Re: ORA-01846: not a valid day of the week
                    Rob van Wijk
                    So here is a way to obtain the next "D" day independently of the NLS_LANGUAGE setting:

                    select trunc(sysdate + 1) + mod(abs(to_number(to_char(sysdate + 1,'D')) - 7) + 2, 7) from dual;

                    Replace "2" in the above statement with the number value of the day that you want, where
                    Sunday = 1
                    Monday = 2
                    Tuesday = 3
                    Wednesday = 4
                    Thursday = 5
                    Friday =6
                    Saturday = 7

                    This works in every language!


                    Not true:
                    SQL> select trunc(to_date('23/06/2007','DD/MM/YYYY'))
                      2         + mod(abs(to_number(to_char(to_date('23/06/2007','DD/MM/YYYY'),'D')) - 7) + 7, 7)
                      3    from dual
                      4  /

                    TRUNC(TO_DATE('23/0
                    -------------------
                    24-06-2007 00:00:00

                    1 rij is geselecteerd.

                    SQL> alter session set nls_territory = 'america'
                      2  /

                    Sessie is gewijzigd.

                    SQL> select trunc(to_date('23/06/2007','DD/MM/YYYY'))
                      2         + mod(abs(to_number(to_char(to_date('23/06/2007','DD/MM/YYYY'),'D')) - 7) + 7, 7)
                      3    from dual
                      4  /

                    TRUNC(TO_
                    ---------
                    23-JUN-07

                    1 rij is geselecteerd.
                    Regards,
                    Rob.
                    • 7. Re: ORA-01846: not a valid day of the week
                      572471
                      Sure, Rob, because to_char(date, 'D') is NLS-dependent :))
                      confusing Tip))
                      • 8. Re: ORA-01846: not a valid day of the week
                        Rob van Wijk
                        I've wondered earlier why in some countries they think Adam and Eve were created on the first day of the week ... ;-)

                        But here is a little function I wrote for a true NLS independent next_day function.
                        And if you like sunday to be considered as the last day of the week, you provide a number 7 and if you sunday to be the first day of the week, you enter a zero.
                        SQL> create or replace function nls_independent_next_day
                          2  ( p_date                   in date
                          3  , p_day_number_of_the_week in number  /* 0 or 7 = Sunday, 1 = Monday, ..., 6 = Saturday */
                          4  ) return date
                          5  is
                          6  begin
                          7    return
                          8      case
                          9      when  p_day_number_of_the_week in (0,1,2,3,4,5,6,7)
                        10      then
                        11        p_date +
                        12        nvl(nullif(mod(6 - mod(to_number(to_char(p_date,'J')),7) + p_day_number_of_the_week,7),0),7)
                        13      end
                        14    ;
                        15  end;
                        16  /

                        Functie is aangemaakt.

                        SQL> alter session set nls_date_format = 'dd-mm-yyyy'
                          2  /

                        Sessie is gewijzigd.

                        SQL> select nls_independent_next_day(sysdate,0) sunday1_1
                          2       , nls_independent_next_day(sysdate,1) monday1
                          3       , nls_independent_next_day(sysdate,2) tuesday1
                          4       , nls_independent_next_day(sysdate,3) wednesday1
                          5       , nls_independent_next_day(sysdate,4) thursday1
                          6       , nls_independent_next_day(sysdate,5) friday1
                          7       , nls_independent_next_day(sysdate,6) saturday1
                          8       , nls_independent_next_day(sysdate,7) sunday1_2
                          9       , nls_independent_next_day(sysdate+1,0) sunday2_1
                        10       , nls_independent_next_day(sysdate+1,1) monday2
                        11       , nls_independent_next_day(sysdate+1,2) tuesday2
                        12       , nls_independent_next_day(sysdate+1,3) wednesday2
                        13       , nls_independent_next_day(sysdate+1,4) thursday2
                        14       , nls_independent_next_day(sysdate+1,5) friday2
                        15       , nls_independent_next_day(sysdate+1,6) saturday2
                        16       , nls_independent_next_day(sysdate+1,7) sunday2_2
                        17    from dual
                        18  /

                        SUNDAY1_1  MONDAY1    TUESDAY1   WEDNESDAY1 THURSDAY1  FRIDAY1    SATURDAY1  SUNDAY1_2
                        ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                        SUNDAY2_1  MONDAY2    TUESDAY2   WEDNESDAY2 THURSDAY2  FRIDAY2    SATURDAY2  SUNDAY2_2
                        ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                        24-06-2007 25-06-2007 26-06-2007 27-06-2007 28-06-2007 29-06-2007 23-06-2007 24-06-2007
                        24-06-2007 25-06-2007 26-06-2007 27-06-2007 28-06-2007 29-06-2007 30-06-2007 24-06-2007


                        1 rij is geselecteerd.
                        Regards,
                        Rob.
                        • 9. Re: ORA-01846: not a valid day of the week
                          572471
                          yeah, what is really NLS-independent is to_char(date, 'J').
                          A solution in SQL could look like:
                          SQL> var p number;
                          SQL> exec :p:=2; /*looking for the next wednesday*/
                          
                          PL/SQL procedure successfully completed
                          p
                          ---------
                          2
                          
                          SQL> 
                          SQL> /*enter values for finding next days
                            2  0 monday
                            3  1 tuesday
                            4  2 wednesday
                            5  3 thursday
                            6  4 friday
                            7  5 saturday
                            8  6 sunday*/
                            9  --
                          SQL> with t as (select :p-mod(to_char(sysdate,'j'),7) diff from dual)
                            2  --
                            3  select trunc(sysdate)+decode(sign(diff),-1,7+diff, 1, diff, 7) from t
                            4  /
                          
                          TRUNC(SYSDATE)+DECODE(SIGN(DIF
                          ------------------------------
                          27.06.2007
                          p
                          ---------
                          2
                          
                          SQL> 
                          • 10. Re: ORA-01846: not a valid day of the week
                            Rob van Wijk
                            Also nice and essentially the same, although the NEXT_DAY function normally does not trunc ...

                            Regards,
                            Rob.