Home » SQL & PL/SQL » SQL & PL/SQL » Help with date format picture ends ....error (Oracle 9i, Windows XP, Crystal Reports XI Release 2)
Help with date format picture ends ....error [message #345475] Wed, 03 September 2008 14:33 Go to next message
kam123
Messages: 13
Registered: January 2008
Junior Member
Hi Everyone,

I am getting the following error when I run my crystal report.

Failed to retrieve data from the database.
Details: HY000:[DataDirect][ODBC Oracle driver][Oracle]ORA-01830: date format picture ends before converting entire input string
[Database Vendor Code: 1830]

I am using SQL Command for my report and in the command my date fields are of "Date" datatype.

I am using Crystal Reports XI R2,driver - CR Oracle ODBC Driver 5.1

Any help is greatly appreciated.

Thanks in advance

My SQL Command is as follows:
with MAXNEWSTAT as
(select 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY,
/*----------------------------------------------*/
HRHISTORY.EMPLOYEE,
MAX(HRHISTORY.ACT_OBJ_ID) ACT_OBJ_ID
FROM
LAWSON.HRHISTORY
WHERE
HRHISTORY.FLD_NBR=20
and TO_DATE(HRHISTORY.DATE_STAMP) 
    >= TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,-1)),'YYYY/MM')||'/01'),'YYYY/MM/DD')  
    AND  TO_DATE(HRHISTORY.DATE_STAMP) <= (TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,0)),'YYYY/MM')||'/01'),'YYYY/MM/DD'))-1
/*----------Added by HAN------------------------------------*/
    GROUP BY COMPANY,
/*----------------------------------------------*/
    EMPLOYEE),
    

NEWSTAT AS
(SELECT 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY,
/*----------------------------------------------*/
HRHISTORY.EMPLOYEE,
HRHISTORY.A_VALUE,
HRHISTORY.DATE_STAMP,
HRHISTORY.BEG_DATE,
HRHISTORY.ACT_OBJ_ID NS_OBJ_ID,
HRHISTORY.SEQ_NBR

FROM
LAWSON.HRHISTORY
INNER JOIN LAWSON.MAXNEWSTAT
ON 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY = MAXNEWSTAT.COMPANY
/*----------------------------------------------*/
/*----------Added by KAM------------------------------------*/
AND HRHISTORY.EMPLOYEE = MAXNEWSTAT.EMPLOYEE
/*----------------------------------------------*/
AND HRHISTORY.ACT_OBJ_ID = MAXNEWSTAT.ACT_OBJ_ID
WHERE
HRHISTORY.FLD_NBR=20
and TO_DATE(HRHISTORY.DATE_STAMP) 
    >= TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,-1)),'YYYY/MM')||'/01'),'YYYY/MM/DD')  
    AND  TO_DATE(HRHISTORY.DATE_STAMP) <= (TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,0)),'YYYY/MM')||'/01'),'YYYY/MM/DD'))-1
    
    ),
    

    
PREVSTATID AS
(SELECT 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY,
/*----------------------------------------------*/
HRHISTORY.EMPLOYEE,
MAX(HRHISTORY.ACT_OBJ_ID)OBJ_ID
FROM
LAWSON.HRHISTORY
    INNER JOIN LAWSON.NEWSTAT
/*----------Added by HAN------------------------------------*/
    ON HRHISTORY.COMPANY = NEWSTAT.COMPANY
/*----------------------------------------------*/
    AND HRHISTORY.EMPLOYEE = NEWSTAT.EMPLOYEE
WHERE
HRHISTORY.FLD_NBR=20
AND TO_DATE(HRHISTORY.BEG_DATE)
< to_date(NEWSTAT.BEG_DATE)
GROUP BY 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY, 
/*----------------------------------------------*/
HRHISTORY.EMPLOYEE
),

PREVSTAT AS
(SELECT 
/*----------Added by HAN------------------------------------*/
HRHISTORY.COMPANY,
/*----------------------------------------------*/
HRHISTORY.EMPLOYEE,
HRHISTORY.A_VALUE A_VALUE,
HRHISTORY.DATE_STAMP DATE_STAMP,
HRHISTORY.BEG_DATE BEG_DATE,
HRHISTORY.ACT_OBJ_ID OBJ_ID,
HRHISTORY.SEQ_NBR SEQ_NBR

FROM
LAWSON.HRHISTORY
    INNER JOIN LAWSON.PREVSTATID
/*----------Added by HAN------------------------------------*/
    ON HRHISTORY.COMPANY = PREVSTATID.COMPANY
/*----------------------------------------------*/
    AND HRHISTORY.EMPLOYEE = PREVSTATID.EMPLOYEE
WHERE
HRHISTORY.FLD_NBR=20 AND
HRHISTORY.ACT_OBJ_ID = PREVSTATID.OBJ_ID
),

MAXPERSACTHST AS
(SELECT
PERSACTHST.EMPLOYEE,
/*----------Added by KAM------------------------------------*/

PERSACTHST.COMPANY,
/*----------------------------------------------*/
MAX(PERSACTHST.DATE_STAMP)DATE_STAMP
FROM
LAWSON.PERSACTHST
WHERE
 (PERSACTHST.ACTION_CODE='LOASTATUS' OR PERSACTHST.ACTION_CODE='STATUS') 
AND TO_DATE(PERSACTHST.DATE_STAMP) 
    >= TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,-1)),'YYYY/MM')||'/01'),'YYYY/MM/DD')  
    AND  TO_DATE(PERSACTHST.DATE_STAMP) <= (TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,0)),'YYYY/MM')||'/01'),'YYYY/MM/DD'))-1
GROUP BY PERSACTHST.EMPLOYEE, 
/*----------Added by KAM------------------------------------*/
PERSACTHST.COMPANY
/*----------------------------------------------*/
    
    ),


    
 CHANGELIST AS    

 (SELECT
 PERSACTHST.EMPLOYEE,
/*----------Added by KAM------------------------------------*/
 PERSACTHST.COMPANY, 
/*----------------------------------------------*/
 PERSACTHST.ACTION_CODE,
 PERSACTHST.REASON_01, 
 PERSACTHST.DATE_STAMP, 
 PERSACTHST.EFFECT_DATE,
 PERSACTHST.REASON_02,
 PREVSTAT.A_VALUE PS_A_VALUE,
 PREVSTAT.DATE_STAMP PS_HR_DATE_STAMP,
 PREVSTAT.BEG_DATE PS_HR_BEG_DATE,
 PREVSTAT.OBJ_ID PS_HR_OBJ_ID,
 PREVSTAT.SEQ_NBR PS_HR_SEQ_ID,
 NEWSTAT.A_VALUE NS_A_VALUE,
 NEWSTAT.DATE_STAMP NS_DATE_STAMP,
 NEWSTAT.BEG_DATE NS_BEG_DATE,
 NEWSTAT.NS_OBJ_ID,
 NEWSTAT.SEQ_NBR NS_SEQ_NBR
 
 
 FROM
 LAWSON.PERSACTHST PERSACTHST
                    INNER JOIN LAWSON.PREVSTAT
                   ON PERSACTHST.EMPLOYEE=PREVSTAT.EMPLOYEE
                   /*----------Added by KAM------------------------------------*/
                   AND PERSACTHST.COMPANY = PREVSTAT.COMPANY
                   /*----------------------------------------------*/
                   INNER JOIN LAWSON.NEWSTAT
                   ON PERSACTHST.EMPLOYEE = NEWSTAT.EMPLOYEE
                  /*----------Added by KAM------------------------------------*/
                   AND PERSACTHST.COMPANY = NEWSTAT.COMPANY
                  /*----------------------------------------------*/
                   INNER JOIN LAWSON.MAXPERSACTHST
                   ON PERSACTHST.EMPLOYEE = MAXPERSACTHST.EMPLOYEE
                  /*----------Added by KAM------------------------------------*/
                     AND PERSACTHST.COMPANY = MAXPERSACTHST.COMPANY
                   /*----------------------------------------------*/
 WHERE

 (PERSACTHST.ACTION_CODE='LOASTATUS' OR PERSACTHST.ACTION_CODE='STATUS')
 AND NEWSTAT.A_VALUE <> PREVSTAT.A_VALUE
 AND MAXPERSACTHST.DATE_STAMP = PERSACTHST.DATE_STAMP
 ),
 

 
 PAEMPPOSENDDATE AS
 (SELECT
  PAEMPPOS.EMPLOYEE,
  PAEMPPOS.COMPANY,
  CASE 
  WHEN PAEMPPOS.END_DATE = TO_DATE('1700,01,01','YYYY,MM,DD')
         THEN to_date(SYSDATE + 1,'YYYY,MM,DD')
       ELSE PAEMPPOS.END_DATE  
  END END_DATE,
  PAEMPPOS.EFFECT_DATE
  FROM LAWSON.PAEMPPOS),
 

 CURRFTE AS
 (SELECT
  PAEMPPOS.EMPLOYEE,
/*----------Added by KAM------------------------------------*/
  PAEMPPOS.COMPANY,
/*----------------------------------------------*/
  PAEMPPOS.FTE CURR_FTE,
  PAEMPPOS.EFFECT_DATE,
  PAEMPPOSENDDATE.END_DATE   
 FROM
 LAWSON.PAEMPPOS
 INNER JOIN LAWSON.NEWSTAT
 ON (PAEMPPOS.EMPLOYEE = NEWSTAT.EMPLOYEE)
/*----------Added by KAM------------------------------------*/
 AND (PAEMPPOS.COMPANY = NEWSTAT.COMPANY)
/*----------------------------------------------*/
 INNER JOIN LAWSON.PAEMPPOSENDDATE
 ON PAEMPPOS.EMPLOYEE = PAEMPPOSENDDATE.EMPLOYEE
/*----------Added by KAM------------------------------------*/
 AND PAEMPPOS.COMPANY = PAEMPPOSENDDATE.COMPANY
/*----------------------------------------------*/
 AND PAEMPPOS.EFFECT_DATE=PAEMPPOSENDDATE.EFFECT_DATE
 WHERE
  (PAEMPPOSENDDATE.EFFECT_DATE <= NEWSTAT.BEG_DATE AND PAEMPPOSENDDATE.END_DATE >= NEWSTAT.BEG_DATE)
),



 PREVFTE AS
 (SELECT
  PAEMPPOS.EMPLOYEE,
/*----------Added by KAM------------------------------------*/
  PAEMPPOS.COMPANY,
/*----------------------------------------------*/
  PAEMPPOS.FTE PREV_FTE,
 PAEMPPOSENDDATE.END_DATE  
 FROM
 LAWSON.PAEMPPOS
 INNER JOIN LAWSON.NEWSTAT
 ON (PAEMPPOS.EMPLOYEE = NEWSTAT.EMPLOYEE)
/*----------Added by KAM------------------------------------*/
AND (PAEMPPOS.COMPANY = NEWSTAT.EMPLOYEE)
/*----------------------------------------------*/
 INNER JOIN LAWSON.PAEMPPOSENDDATE
 ON (PAEMPPOS.EMPLOYEE = PAEMPPOSENDDATE.EMPLOYEE)
/*----------Added by KAM------------------------------------*/
AND (PAEMPPOS.COMPANY = PAEMPPOSENDDATE.COMPANY)
/*----------------------------------------------*/
 AND (PAEMPPOS.EFFECT_DATE = PAEMPPOSENDDATE.EFFECT_DATE)
 WHERE
  PAEMPPOS.EFFECT_DATE <= (NEWSTAT.BEG_DATE-1)AND  PAEMPPOSENDDATE.END_DATE >= (NEWSTAT.BEG_DATE -1)
)


 

 
 
 SELECT DISTINCT
 EMPLOYEE.EMPLOYEE,
 EMPLOYEE.DEPARTMENT,
 EMPLOYEE.PROCESS_LEVEL,
 EMPLOYEE.EMP_STATUS,
 EMPLOYEE.FIRST_NAME,
 EMPLOYEE.LAST_NAME,
 EMPLOYEE.MIDDLE_INIT,
 EMPLOYEE.POSITION,
 PAPOSITION.DESCRIPTION,
 CHANGELIST.PS_A_VALUE,
/*----------Added by KAM------------------------------------*/
 CHANGELIST.COMPANY,
/*----------------------------------------------*/
 CHANGELIST.PS_HR_DATE_STAMP,
 CHANGELIST.PS_HR_BEG_DATE,
 CHANGELIST.PS_HR_OBJ_ID,
 CHANGELIST.NS_A_VALUE,
 CHANGELIST.NS_DATE_STAMP,
 CHANGELIST.NS_OBJ_ID,
 CHANGELIST.ACTION_CODE,
 CHANGELIST.REASON_01,
 CHANGELIST.REASON_02,
 CHANGELIST.DATE_STAMP PERSACTSDATESTAMP,
 CHANGELIST.EFFECT_DATE PERSACTEFFDATE,
 DEPTCODE.R_NAME DEPTNAME,
 PRSYSTEM.R_NAME PLNAME,
 PREVFTE.PREV_FTE,
 CURRFTE.CURR_FTE, 
 CHANGELIST.NS_BEG_DATE,
 PGSELECT.GROUP_NAME,
 PAEMPLOYEE.SENIOR_DATE

 
 
 FROM
 LAWSON.CHANGELIST
                  INNER JOIN LAWSON.EMPLOYEE
                 ON (EMPLOYEE.EMPLOYEE = CHANGELIST.EMPLOYEE)
/*----------Added by KAM------------------------------------*/
                 AND (EMPLOYEE.COMPANY = CHANGELIST.COMPANY)
/*----------------------------------------------*/
                 
                 INNER JOIN LAWSON.DEPTCODE
                 ON ((EMPLOYEE.COMPANY=DEPTCODE.COMPANY)
                 AND (EMPLOYEE.PROCESS_LEVEL=DEPTCODE.PROCESS_LEVEL)
                 AND (EMPLOYEE.DEPARTMENT=DEPTCODE.DEPARTMENT))
                 
                 INNER JOIN LAWSON.PRSYSTEM
                 ON ((EMPLOYEE.COMPANY=PRSYSTEM.COMPANY)
                 AND(EMPLOYEE.PROCESS_LEVEL=PRSYSTEM.PROCESS_LEVEL))
                 
                 LEFT OUTER JOIN LAWSON.PREVFTE 
                   ON (CHANGELIST.EMPLOYEE=PREVFTE.EMPLOYEE)
/*----------Added by KAM------------------------------------*/                 
                 AND (CHANGELIST.COMPANY=PREVFTE.COMPANY)
/*----------------------------------------------*/
                 LEFT OUTER JOIN LAWSON.CURRFTE
                 ON (CHANGELIST.EMPLOYEE=CURRFTE.EMPLOYEE)
/*----------Added by KAM------------------------------------*/                                 
                 AND (CHANGELIST.COMPANY=CURRFTE.COMPANY)
/*----------------------------------------------*/
                 INNER JOIN LAWSON.PGSELECT PGSELECT 
                   ON ((EMPLOYEE.COMPANY=PGSELECT.COMPANY)
                 AND (EMPLOYEE.EMP_STATUS=PGSELECT.BEGIN_VALUE))
                 
                 LEFT OUTER JOIN LAWSON.PAPOSITION
                 ON (EMPLOYEE.POSITION=PAPOSITION.POSITION)
                 AND (EMPLOYEE.COMPANY=PAPOSITION.COMPANY)
                 
                 INNER JOIN LAWSON.PAEMPLOYEE
                 ON (CHANGELIST.EMPLOYEE=PAEMPLOYEE.EMPLOYEE)
/*----------Added by KAM------------------------------------*/                
                AND (CHANGELIST.COMPANY=PAEMPLOYEE.COMPANY)
/*----------------------------------------------*/
                 

                 
 WHERE

 (PGSELECT.GROUP_NAME='G:ACTIVE' OR PGSELECT.GROUP_NAME='G:INACTIVE')


Re: Help with date format picture ends ....error [message #345476 is a reply to message #345475] Wed, 03 September 2008 14:44 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi, try checking those:

and TO_DATE(HRHISTORY.DATE_STAMP) 


put a format mask like to_date(v_dt, 'mm/dd/rrr') base on the format of the column/variable your passing.
Re: Help with date format picture ends ....error [message #345477 is a reply to message #345475] Wed, 03 September 2008 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

when was the last time this worked & what has changed since then?

Why are you using TO_DATE on fields which appear already to be date datatypes?
Re: Help with date format picture ends ....error [message #345482 is a reply to message #345475] Wed, 03 September 2008 15:10 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Consider I have a column with a string values of mixed types like '09/23/2008 11:23:44 PM' and '09/23/2008' So if you try to do

    to_date(column_name, 'MM/DD/YYYY')


you will get the error mentioned. To avoid this make your code to handle the timestamp part like the following code:

    to_date(column_name, 'MM/DD/YYYY HH:MI:SS')


OR

Trim out the timestamp part of the string variable and then use it in your normal to_date() query.

Regards,
Jo
Previous Topic: Update/Select
Next Topic: Indexes
Goto Forum:
  


Current Time: Tue Dec 06 06:23:31 CST 2016

Total time taken to generate the page: 0.13671 seconds