ORA-01843: not a valid month when use to-date

[Fatenodeh]

Hello everyone,

I use sql statement to import data from oracle database. I receive "ORA-01843: not a valid month" error when try to connect.

there is something wrong with date format but don't know how to fix it, any suggestions please?

SELECT
  ARADMIN.HPD_HELP_DESK.INCIDENT_NUMBER,
  ARADMIN.HPD_HELP_DESK.DESCRIPTION,
  decode(ARADMIN.HPD_HELP_DESK.PRIORITY,0,'Critical',1,'High',2,'Medium',3,'Low' ) ,
  ARADMIN.HPD_HELP_DESK.PRODUCT_NAME,
  decode(ARADMIN.HPD_HELP_DESK.STATUS,0,'New',1,'Assigned',2,'In Progress',3,'Pending',4,'Resolved',5,'Closed',6,'Cancelled',7,'Rejected'),
  ( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.SUBMIT_DATE  /( 60*60*24 ) ) ),

   ( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.LAST_RESOLVED_DATE /( 60*60*24 ) ) ),
  ARADMIN.HPD_HELP_DESK.DETAILED_DECRIPTION,
  ARADMIN.HPD_HELP_DESK.ASSIGNEE,
  ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_1,
  ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_2,
  ARADMIN.HPD_HELP_DESK.PRODUCT_CATEGORIZATION_TIER_3,
  ARADMIN.HPD_HELP_DESK.CATEGORIZATION_TIER_1,
  ARADMIN.HPD_HELP_DESK.CATEGORIZATION_TIER_2,
  ARADMIN.CTM_PEOPLE.FULL_NAME,
  ARADMIN.HPD_HELP_DESK.ASSIGNED_GROUP,
  ARADMIN.HPD_HELP_DESK_RESOLUTION.DIF_DAY,
  ARADMIN.HPD_HELP_DESK.Z1D_TEMPLATE_NAME
FROM
  ARADMIN.HPD_HELP_DESK,
  ARADMIN.CTM_PEOPLE,
  ARADMIN.HPD_HELP_DESK_RESOLUTION
WHERE
  ( ARADMIN.HPD_HELP_DESK.CORPORATE_ID=ARADMIN.CTM_PEOPLE.CORPORATE_ID(+)  )
  AND  ( ARADMIN.HPD_HELP_DESK.INCIDENT_NUMBER=ARADMIN.HPD_HELP_DESK_RESOLUTION.INCIDENT_NUMBER(+)  )
  AND  
  (
   ARADMIN.HPD_HELP_DESK.ASSIGNED_GROUP  IN  ( 'IT Helpdesk','Technical Support'  )
   AND
    ( TO_DATE('01/01/1970 2:00:00','DD/MM/YYYY HH24:MI:SS')+(ARADMIN.HPD_HELP_DESK.LAST_RESOLVED_DATE /( 60*60*24 ) ) )  >=  '01-01-2020 00:00:00'
   AND
   decode(ARADMIN.HPD_HELP_DESK.STATUS,0,'New',1,'Assigned',2,'In Progress',3,'Pending',4,'Resolved',5,'Closed',6,'Cancelled',7,'Rejected')  IN  ( 'Closed','Resolved'  )
  )

 Thanks,

[PAVAN515]

SELECT /*+ INDEX(POLICY_WORK I_AR_DT) */
"TIPS"."POLICY_WORK"."AGENT_ID" AGENT_ID,
SUM(NVL("TIPS"."POLICY_WORK"."ADJ_RATED_EXPSRE_AMT", 0)) RATED_EXPSRE_AMT,
SUM(NVL("TIPS"."POLICY_WORK"."ADJ_PREM_AMT",0)) FUND_PREM,
SUM(NVL(DECODE("TIPS"."POLICY_WORK"."PRMLGTD_AMT","TIPS"."POLICY_WORK"."ADJ_PRMLGTD_AMT",1,0),0)) COUNT
FROM "TIPS"."POLICY_WORK"
WHERE "TIPS"."POLICY_WORK"."AGENT_ID" = :a_agent_id AND
TO_DATE(TO_CHAR( "TIPS"."POLICY_WORK"."AR_DT",'MM/dd/yyyy') ,'MM/dd/yyyy') BETWEEN TO_DATE('01-01-'||TO_CHAR(TO_CHAR(TO_DATE(:a_start_date,'MM/dd/yyyy'),'YYYY')),'MM/dd/yyyy')AND
TO_DATE('01-01-'||TO_CHAR(TO_CHAR(tO_DATE(:a_end_date,'MM/dd/yyyy'),'YYYY')),'MM/dd/yyyy') AND

( "TIPS"."POLICY_WORK"."TRAN_ID" > 999 )
GROUP BY "TIPS"."POLICY_WORK"."AGENT_ID"

[edhans · Accepted solution]

You may be better off asking this in an Oracle SQL forum to help you with that syntax.  T-SQL in SQL Server uses this format, but that is probably specific to their platform.