Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fatenodeh
Regular Visitor

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

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,

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

edhans_0-1615855112516.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
PAVAN515
Frequent Visitor

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
Super User
Super User

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.

edhans_0-1615855112516.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors