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