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

 

[Turkbear]

Hi,
This section ( where the problem is) seems more complex that t needs to be:

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

If HRHISTORY.DATE_STAMP is an Oracle DATE field, then simple DATE math ( Like SYSDATE - 1 ) should be enough.

If it is not a DATE type , you need a DATE format string in your TO_DATE(HRHISTORY.DATE_STAMP) function.



To Paraphrase:"The Help you get is proportional to the Help you give.."