Getting [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01830: date format picture ends before converting entire input string error in GEL Script

[Anon Anon]

Hi All,

 

I am getting below shown error while after running a process, the same SQL works fine in SQL Developer, can you please help in this.

 

 

org.apache.commons.jelly.JellyTagException: null:19:88: <sql:query> SELECT dp.ts_dp0approved, dp.ts_dp1approved, dp.ts_dp2approved, dp.ts_dp3approved, dp.ts_dp4approved, dp.ts_dp5approved, dp.ts_dp6approved, dp.ts_dp0planned, dp.ts_dp1planned, dp.ts_dp2planned, dp.ts_dp3planned, dp.ts_dp4planned, dp.ts_dp5planned, dp.ts_dp6planned, CASE WHEN (SELECT (TO_DATE(SYSDATE, 'yyyy-MM-dd') - TO_DATE(cap.ts_next_dp_date, 'yyyy-MM-dd')) FROM DUAL) <0 THEN NULL ELSE (SELECT (TO_DATE(SYSDATE, 'yyyy-MM-dd') - TO_DATE(cap.ts_next_dp_date, 'yyyy-MM-dd')) FROM DUAL) END AS DP_DAYS_LATE FROM inv_investments inv, odf_ca_ts_decision_point dp, prtask tsk, odf_ca_project cap WHERE tsk.prid=dp.ts_dpms_task AND cap.id=inv.id AND dp.last_updated_date= (SELECT MAX(dpl.last_updated_date) FROM odf_ca_ts_decision_point dpl WHERE dpl.odf_parent_id=inv.id) AND dp.odf_parent_id=inv.id: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01830: date format picture ends before converting entire input string

[David Morton]

That Oracle error is complaining about date format masks - but you should not even need to be using any format masks I think;

 

If your variable (ts_next_dp_date) is defined as a DATE type, then you do not need to use "TO_DATES" on it, in fact this is probably the error (you only need a TO_DATE to convert a character string of a known format to a DATE type).

 

You certainly should never use TO_DATE on SYSDATE since that is already a DATE!!!

 

--

 

(It might be "working" in SQL*Developer just because SQL*Developer is protecting you against "silly mistakes" - SQL*Developer's run-time environment is different to that of code running under GEL)

[Anon Anon]

Many many thanks dave it works

[David Morton]

...and you don't need to "SELECT ..... FROM DUAL" - you already have the tables/columns in your query, so your CASE statement should just read something like;

 

...CASE WHEN ( SYSDATE - cap.ts_next_dp_date ) <0 THEN NULL ELSE ( SYSDATE - cap.ts_next_dp_date) END AS DP_DAYS_LATE...