Pls Help with Date format picture ends.....error

[gtb12314]

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

 

[Dagon]

I'd look at all the instances where you have used to_date but not put an explicit format e.g.

TO_DATE(HRHISTORY.BEG_DATE)

Check first that the column you are to_dating isn't already a date, in which case you can get rid of the to_date. Then check that the format of the column matches your NLS date format on both client and server. Better yet, change the SQL to add an explicit format mask in all cases.

You could also simplify some of the other date functions e.g.

TO_DATE((TO_CHAR((ADD_MONTHS(SYSDATE,-1)),'YYYY/MM')||'/01'),'YYYY/MM/DD')

can be done using

trunc(add_months(sysdate,-1), 'MM')

 

[BJCooperIT]

This code:

CASE 
  WHEN PAEMPPOS.END_DATE = TO_DATE('1700,01,01','YYYY,MM,DD')
         THEN [COLOR=red]to_date(SYSDATE + 1,'YYYY,MM,DD')[/color]

SYSDATE is already a date, so remove the TO_DATE.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com