9 Replies Latest reply: Oct 17, 2008 3:31 PM by 654082 RSS

    ORA-01843 not a valid month?

    654082
      I have a query in which I'm getting the error mentioned above (ORA-01843 not a valid month), I have tried different things to solve it, but nothing seems to work. The problem is with the line before the last one. How could I fix this? Any suggestions?
      I'll appreciate your help.
      (By the way, on that line I'm doing some strange conversion because the dates in the database are store in "Unix Epoch Time format" http://www.epochconverter.com/)
      SELECT
        ARADMIN.HPD_HELP_DESK.INCIDENT_NUMBER,
        --to_char((ARADMIN.HPD_HELP_DESK.CLOSED_DATE/86400)+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'),'Month'),
        ResolvedByGroup.SUPPORT_GROUP_NAME,
        ARADMIN.CTM_SUPPORT_GROUP.SUPPORT_GROUP_NAME,
        ARADMIN.HPD_HELP_DESK_ASSIGNMENT_LOG.NUMBER_OF_TRANSFERS,
        --trunc((ARADMIN.HPD_HELP_DESK.LAST_RESOLVED_DATE/86400)+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM')),
        --TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM')),
        ARADMIN.CTM_SUPPORT_GROUP_ASSOC_LOOKUP.SUPPORT_GROUP_NAME
      FROM
        ARADMIN.HPD_HELP_DESK_ASSIGNMENT_LOG,
        ARADMIN.HPD_HELP_DESK,
        ARADMIN.CTM_SUPPORT_GROUP  ResolvedByGroup,
        ARADMIN.CTM_SUPPORT_GROUP,
        ARADMIN.CTM_SUPPORT_GROUP_ASSOC_LOOKUP
      WHERE
        ( ARADMIN.HPD_HELP_DESK_ASSIGNMENT_LOG.INCIDENT_ENTRY_ID(+)=ARADMIN.HPD_HELP_DESK.ENTRY_ID  )
        AND  ( ARADMIN.CTM_SUPPORT_GROUP_ASSOC_LOOKUP.LOGIN_ID=ARADMIN.HPD_HELP_DESK.SUBMITTER  )
        AND  ( ARADMIN.CTM_SUPPORT_GROUP_ASSOC_LOOKUP.DEFAULT_X=0  )
        AND  ( ARADMIN.CTM_SUPPORT_GROUP.SUPPORT_GROUP_ID(+)=ARADMIN.HPD_HELP_DESK.ASSIGNED_GROUP_ID  )
        AND  ( ARADMIN.HPD_HELP_DESK.RESOLVED_BY_GROUP=ResolvedByGroup.SUPPORT_GROUP_NAME(+)  )
        AND  
        (
         ARADMIN.CTM_SUPPORT_GROUP_ASSOC_LOOKUP.SUPPORT_GROUP_NAME  In  ( 'CHQ-SERVICEDESK','CHQ-Z-SERVICEDESK'  )
         AND
         TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))  BETWEEN  '14-10-2008 00:00:00'  AND  '15-10-2008 00:00:00'
        )
        • 1. Re: ORA-01843 not a valid month?
          Gurjas
          Change the last line from
             TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))  BETWEEN  '14-10-2008 00:00:00'  AND  '15-10-2008 00:00:00' 
          to this
           TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))  BETWEEN  to_date('14-10-2008', 'dd-mm-yyyy')  AND  to_date('15-10-2008', 'dd-mm-yyyy')
          It will automatically include 00:00:00 at the end for the date

          Ya aweiden is right you can't user date/number (ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400)
          and Date + date is also wrong (ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400 +TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))

          Edited by: s_2 on Oct 17, 2008 12:14 AM
          • 2. Re: ORA-01843 not a valid month?
            Andreas Weiden
            TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM')) BETWEEN '14-10-2008 00:00:00' AND '15-10-2008 00:00:00'
            TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))  
            BETWEEN  TO_DATE('14-10-2008 00:00:00','DD-MM-YYYY HH24:MI:SS')  AND  TO_DATE('15-10-2008 00:00:00','DD-MM-YYYY HH24:MI:SS')
            or
            TRUNC(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400+TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))  
            BETWEEN  TO_DATE('14-10-2008','DD-MM-YYYY')  AND  TO_DATE('15-10-2008','DD-MM-YYYY')
            but
            - what datatype is ARADMIN.HPD_HELP_DESK.SUBMIT_DATE, if its a date there will be another error
            SQL> select sysdate/86400 from dual;
            select sysdate/86400 from dual
                   *
            FEHLER in Zeile 1:
            ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, DATE erhalten
            - why are you mixing different date formats?
            • 3. Re: ORA-01843 not a valid month?
              Frank Kulash
              Hi,

              Never use a string where a DATE is expected. Always use TO_DATE to explicitly convert the string to a DATE.
              TRUNC ((ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400) + TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))
                  BETWEEN  TO_DATE ('14-10-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                      AND  TO_DATE ('15-10-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                )
              Is there a good reason for using two different date formats? I find it confusing.
              • 4. Re: ORA-01843 not a valid month?
                654082
                The dates on this database are stored as NUMBER(15,0)
                Te reason it's because it's created by a program called "BMC Remedy" and that's how that programs works, I guess.
                Thanks for all of your suggestions... I'll still need to work on this query a little more
                • 5. Re: ORA-01843 not a valid month?
                  Andreas Weiden
                  The dates on this database are stored as NUMBER(15,0)
                  good information, would have helped to have it before.
                  I'll still need to work on this query a little more
                  does it work or not? if not, perhaps you could post some sample data...
                  • 6. Re: ORA-01843 not a valid month?
                    Sentinel
                    The OP did state that the dates were stored in "UNIX Epoch Time Format" plus gave a link to a epoch time converter web page from wich you can see that epoch time is simply a number. More specifically epoch time is the number of seconds since midnight on 1 Jan, 1970 Greenwich Mean Time (GMT)

                    A more robust method of converting an epoch time to an oracle data type would be to use this formula:
                    to_timestamp_tz('1970-01-01 GMT','RRRR-MM-DD TZR')+numtodsinterval(epoch_time,'second')
                    This results in a timestamp with time zone based on GMT time. To convert to your local time simply cast it to a timestamp with local time zone:
                    cast(to_timestamp_tz('1970-01-01 GMT','RRRR-MM-DD TZR')+numtodsinterval(epoch_time,'second') as timestamp with local time zone)
                    or assign it to a timestamp with local time zone variable
                    declare
                      ltz timestamp with local time zone;
                      epoch_time number := 1224184932;
                    begin
                      ltz := to_timestamp_tz('1970-01-01 GMT','RRRR-MM-DD TZR')+numtodsinterval(epoch_time,'second');
                    end;
                    /
                    Edited by: Sentinel on Oct 16, 2008 12:23 PM
                    • 7. Re: ORA-01843 not a valid month?
                      Andreas Weiden
                      your right, simply overread it ;-)
                      • 8. Re: ORA-01843 not a valid month?
                        Frank Kulash
                        Hi,

                        You should consider changing the way dates are stored. (You probably already have.)

                        If you're stuck using "unix epoch" format, you might consider writing PL/SQL functions to convert between that format and Oracle DATE.

                        For example, in a package you could have:
                        --          ***********************************
                        --          **   t o _ u n i x _ e p o c h   **
                        --          ***********************************
                        
                        --     to_unix_epoch returns the number of seconds from
                        --     midnight on January 1, 1970 (UT) to in_dt.
                        --     If in_dt is before 1970, the value returned will be negative.
                        
                        FUNCTION     to_unix_epoch
                        (     in_dt     IN     DATE
                        )
                        RETURN     NUMBER
                        DETERMINISTIC
                        IS
                        BEGIN
                             RETURN     (
                                  in_dt - TO_DATE     ( '31-Dec-1969 17:00:00'
                                            , 'DD-Mon-YYYY HH24:MI:SS'
                                            )
                                  ) * 24 * 60 * 60;
                        END     to_unix_epoch
                        ;
                        You could overlay this function with another that takes a string argument, converts it to a DATE, and calls the function above:
                        FUNCTION     to_unix_epoch
                        (     in_dt_txt     IN     VARCHAR2
                        ,     in_format_txt     IN     VARCHAR2     DEFAULT     'DD-Mon-YYYY HH24:MI:SS'
                        )
                        RETURN     NUMBER
                        DETERMINISTIC
                        IS
                        BEGIN
                             RETURN     to_unix_epoch (TO_DATE (in_dt_txt, in_format_txt));
                        END     to_unix_epoch
                        ;
                        Or you could make stand-alone functions (just change the beginning to "CREATE OR REPLACE FUNCTION ...").
                        Stand-alone functions can't be overloaded, so you'd have to change the name of one of them.

                        Your queries may run faster if you say:
                        WHERE   ARADMIN.HPD_HELP_DESK.SUBMIT_DATE
                                    BETWEEN  my_dt_pkg.to_unix_epoch ('14-10-2008 00:00:00')
                                        AND  my_dt_pkg_to_unix_epoch ('15-10-2008 00:00:00')
                          )
                        instead of
                        WHERE   TRUNC ((ARADMIN.HPD_HELP_DESK.SUBMIT_DATE/86400) + TO_DATE('12/31/1969 5:00:00 PM','MM/DD/YYYY HH:MI:SS AM'))
                                    BETWEEN  TO_DATE ('14-10-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                                        AND  TO_DATE ('15-10-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
                          )
                        By keeping the database column intact in the condition, you allow the optimizer to use an index on it.

                        To make things even faster, don't call PL/SQL at all. The function to_unix_epoch is so simple, you can code it directly into your query.
                        • 9. Re: ORA-01843 not a valid month?
                          654082
                          @aweiden
                          It does work, but not for my purposes... but I guess that's my fault.

                          Thanks everyone for all of your help!