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

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

>

[former_member11696]

You are using Lawson DB, they are an OEM partner of ours and therefore they will support your DB connection issues.

[Former Member]

Hi Arsh,

The error message you receive i.e.

ORA-01830: date format picture ends before converting entire input string

is an Oracle error.

The following document would be helpful:

ORA-01830: date format picture ends before converting entire input string

Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.

Action: Check the specifications for date format pictures and correct the statement.

Regards,

Alpana

[former_member260594]

Arsh,

Does this SQL run outside of Crystal Reports?

Can you create a stored procedure with the same syntax and does it run?

What happens if you use a Native connection to your Oracle DB or Oracles ODBC driver?

[Former Member]

Hi,

Sorry for the late reply becos I was off from work.

Yes this SQL runs perfect in TOAD for oracle without any error.

I don't know how to create a stored procedure that's why I didn't try that.

I tried running the same SQL in crystal reports XI by using CR Oracle ODBC Driver 5.1 but I got the error as below:

ORA - 01830: date format picture ends before converting entire input string.

Also I will will try with the native connection.

Thank You