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!

ORA-01843 not a valid month?

654082Oct 16 2008 — edited Oct 17 2008
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'
  )
This post has been answered by Gurjas on Oct 16 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2008
Added on Oct 16 2008
9 comments
1,989 views