1 2 Previous Next 17 Replies Latest reply on Dec 19, 2010 8:00 AM by andyschwarz

    ORA-01846: not a valid day of the week

    baskar.l
      Hi all,

      Good day,

      While executing the below query i am getting the error as

      SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
        2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
        3         round((start_time-last_time)*24*60,2)mins_down,
        4         round((last_time-lag(start_time) over (order by r)),2) days_up,
        5         case when (lead(r) over (order by r) is null)
        6              then round((sysdate-start_time),2)
        7         end days_still_up
        8    from (
        9   select r,
       10         to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
       11         to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
       12    from (
       13   select r,
       14          text_line,
       15         lag(text_line,1) over (order by r) start_time,
       16         lag(text_line,2) over (order by r) last_time
       17    from (
       18   select rownum r,text_line
       19    from alert_log_steeld6
       20   where text_line like '__ __ __ __:__:__20__'
       21       or text_line like 'Starting ORACLE instance %'
       22            )
       23            )
       24   where text_line like 'Starting ORACLE instance %'
       25            )
       26  /
      ERROR:
      ORA-01846: not a valid day of the week
      
      
      
      no rows selected
      Please suggest on the same...

      thanks,
      baskar.l
        • 1. Re: ORA-01846: not a valid day of the week
          009
          Hi,
          Refer ora-01846, ora-01846

          *009*

          Edited by: 009 on Apr 8, 2010 11:18 PM
          • 2. Re: ORA-01846: not a valid day of the week
            MScallion
            It looks like last_time and start_time are dates and you are trying to convert them to_date instead of to_char.
            • 3. Re: ORA-01846: not a valid day of the week
              baskar.l
              hi,

              My last time and start time looks like this 'Sat Nov 21 21:08:07 2009'

              thanks,
              baskar.l
              • 4. Re: ORA-01846: not a valid day of the week
                baskar.l
                hi,

                when i try to convert this as to_char i get
                SQL> select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
                  2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
                  3         round((start_time-last_time)*24*60,2)mins_down,
                  4         round((last_time-lag(start_time) over (order by r)),2) days_up,
                  5         case when (lead(r) over (order by r) is null)
                  6              then round((sysdate-start_time),2)
                  7         end days_still_up
                  8    from (
                  9   select r,
                 10         to_char(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
                 11         to_char(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
                 12    from (
                 13   select r,
                 14          text_line,
                 15         lag(text_line,1) over (order by r) start_time,
                 16         lag(text_line,2) over (order by r) last_time
                 17    from (
                 18   select rownum r,text_line
                 19    from alert_log_steeld6
                 20   where text_line like '__ __ __ __:__:__20__'
                 21       or text_line like 'Starting ORACLE instance %'
                 22            )
                 23            )
                 24   where text_line like 'Starting ORACLE instance %'
                 25            )
                 26  /
                            then round((sysdate-start_time),2)
                                                            *
                ERROR at line 6:
                ORA-00932: inconsistent datatypes: expected DATE got NUMBER
                thanks,
                baskar.l
                • 5. Re: ORA-01846: not a valid day of the week
                  Twinkle
                  Hi Baskar,

                  Please clear my doubt..
                  select r,
                   10         to_char(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
                   11         to_char(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
                   12    from 
                  Above is an inline query,

                  The final display in the outer most SQL is in
                  to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
                    2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
                  
                  
                  
                  select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
                    2         to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
                    3         round((start_time-last_time)*24*60,2)mins_down,
                    4         round((last_time-lag(start_time) over (order by r)),2) days_up,
                    5         case when (lead(r) over (order by r) is null)
                    6              then round((sysdate-start_time),2)
                    7         end days_still_up
                    8    from (
                  Then why in the inner query you are converting in
                  to_char(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time or to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
                  What is the data type of
                  text_line
                  Twinkle
                  • 6. Re: ORA-01846: not a valid day of the week
                    baskar.l
                    Hi ,

                    The text_line format in the file is like this
                    Sat Nov 21 21:08:07 2009
                    Starting ORACLE instance (normal)
                    thanks,
                    baskar.l
                    • 7. Re: ORA-01846: not a valid day of the week
                      MScallion
                      OK, I reformatted your query so that I could understand it better. As you are converting the text_line to_date then it is likely that there is an issue with your data. It might be best to execute the first 3 inner queries and display the text_line without converting it to_date. this will show the data that is being applied to the date conversion.

                      It appears that alert_log_steeld6 is n external table as you are using the rownum from the innermost query without a sort.
                      • 8. Re: ORA-01846: not a valid day of the week
                        baskar.l
                        yeah,

                        I created a external table and then using this query to get the output. Reformatted the query. Can you please post that?

                        thanks,
                        baskar.l
                        • 9. Re: ORA-01846: not a valid day of the week
                          Twinkle
                          No,

                          My question was the text_line must be a column name.. So whts the data type of that column?

                          If its a varchar2 then there should not be a problem
                          SQL> with tab as
                            2  (select 'Sat Nov 21 21:08:07 2009' d from dual)
                            3  select to_date(d,'Dy Mon DD HH24:MI:SS YYYY') d from tab; 
                          
                          D
                          ---------
                          21-NOV-09
                          if its a date data type then your error comes..
                           
                          SQL> with tab as
                            2  (select to_date('Sat Nov 21 21:08:07 2009','Dy Mon DD HH24:MI:SS YYYY') d from dual)
                            3  select to_date(d,'Dy Mon DD HH24:MI:SS YYYY') d from tab;
                          select to_date(d,'Dy Mon DD HH24:MI:SS YYYY') d from tab
                                         *
                          ERROR at line 3:
                          ORA-01846: not a valid day of the week
                          So if its in date data type then do not use to_date and date format on it.


                          Twinkle
                          • 10. Re: ORA-01846: not a valid day of the week
                            baskar.l
                            hi,

                            Here is the create table script
                            create table alert_log_steeld6
                              (
                                text_line varchar2(255)
                              )
                             ORGANIZATION EXTERNAL
                             (
                               TYPE ORACLE_LOADER
                               DEFAULT DIRECTORY data_dir_steelp1
                               ACCESS PARAMETERS
                             (
                                records delimited by newline
                                fields
                                REJECT ROWS WITH ALL NULL FIELDS
                             )
                             LOCATION
                             (
                               'alert_STEELP1.log'
                             )
                            )
                            REJECT LIMIT unlimited
                            /
                            thanks,
                            baskar.l
                            • 11. Re: ORA-01846: not a valid day of the week
                              MScallion
                              Reformatted the query. Can you please post that?
                              There is nothing wrong with your format - it is simply personal preference and easier for me to read. I originally assumed that text_line was a date column but looking at your query again it appears to be varchar2.

                              If you remove the outermost query and the to_date functions then you will see the data that is being returned from the inner queries. Is there any unexpected data that will cause the error on the to_date function;
                              select r, last_time, start_time
                              from (select r,
                                           text_line,
                                           lag(text_line, 1) over(order by r) start_time,
                                           lag(text_line, 2) over(order by r) last_time
                                    from (select rownum r, text_line
                                          from alert_log_steeld6
                                          where text_line like '__ __ __ __:__:__20__'
                                          or text_line like 'Starting ORACLE instance %'))
                              where text_line like 'Starting ORACLE instance %';
                              • 12. Re: ORA-01846: not a valid day of the week
                                Twinkle
                                As per the posted table structure and given query I too doubt that there could be unexpected data in your file.
                                SQL> with tab as
                                  2  (select 'Sat Nov 21 21:08:07 2009' d from dual)
                                  3  select to_char(d,'dd-mon-yyyy hh24:mi') shutdown ,round((sysdate-d),2)
                                  4  from(
                                  5  select to_date(d,'Dy Mon DD HH24:MI:SS YYYY') d from tab)
                                  6  ; 
                                
                                SHUTDOWN          ROUND((SYSDATE-D),2)
                                ----------------- --------------------
                                21-nov-2009 21:08               138.66
                                As per your table structure and query, I have simplified it to show you that your query is perfect.
                                But the data inserted might not be uniform as per the format
                                to_date(text_line,'Dy Mon DD HH24:MI:SS YYYY')
                                Twinkle
                                • 13. Re: ORA-01846: not a valid day of the week
                                  baskar.l
                                  hi,

                                  Thanks for your suggestions, but am still confused about how do i re-write this query
                                  select to_date(last_time,'dd-mon-yyyy hh24:mi') shutdown,
                                    2         to_date(start_time,'dd-mon-yyyy hh24:mi') startup,
                                    3         round((start_time-last_time)*24*60,2)mins_down,
                                    4         round((last_time-lag(start_time) over (order by r)),2) days_up,
                                    5         case when (lead(r) over (order by r) is null)
                                    6              then round((sysdate-start_time),2)
                                    7         end days_still_up
                                    8    from (
                                    9   select r,
                                   10         to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
                                   11         to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
                                   12    from (
                                  thanks,
                                  baskar.l
                                  • 14. Re: ORA-01846: not a valid day of the week
                                    825028
                                    hi, baskar.l
                                    Twinkle is right.
                                    i encountered the same problem. and i finally find that there was some unavailable data in the alert_log table.

                                    when you had a not clean shutdown in the alert_log table.
                                    you might have unavailable data like the following(line 391,392), it lacks a shutdown timestamp:

                                    R TEXT_LINE
                                         388     Sat Jul 17 06:04:23 2010
                                         389     Sat Jul 17 06:45:03 2010
                                         390     Starting ORACLE instance (normal)
                                         *391     Sat Jul 17 06:45:43 2010*
                                         *392     Starting ORACLE instance (normal)*

                                    when a clean shutdown there would be a shutdown timestampe(like the 388 line)

                                    line 391 392 indicates it was not a clean shutdown.
                                    to simply work around this issue:
                                    add the where r < 391 clause
                                    or where r > 392 clause to avoid the unavailable data.


                                    select r,
                                    14 text_line,
                                    15 lag(text_line,1) over (order by r) start_time,
                                    16 lag(text_line,2) over (order by r) last_time
                                    17 from (
                                    18 select rownum r,text_line
                                    19 from alert_log_steeld6
                                    20 where text_line like '__ __ __ __:__:__20__'
                                    21 or text_line like 'Starting ORACLE instance %'
                                    22 )
                                    where r < 391


                                    Tsingyee.
                                    1 2 Previous Next