Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with Date Validation...(subtracting).

Highlighted
David Wray
Occasional Advisor

Issue with Date Validation...(subtracting).

Does anyone know the syntax for subtracting dates in PPM? I am trying to calculate the number of days between two dates on a table.

I have tried all kinds of combinations but can't seem to get the validation to work.

I am assuming that the dates are strings in PPM, hence, I should be able to use SQL functions, like TO_DATE on them....right?

Below is the code

SELECT (TO_DATE('[TE.P.PS_ACTUAL_START_DT]','YYYY-MM-DD HH24:MI:SS') - TO_DATE('[TE.P.PS_PLANNED_START_DT]','YYYY-MM-DD HH24:MI:SS')), (TO_DATE('[TE.P.PS_ACTUAL_START_DT]','YYYY-MM-DD HH24:MI:SS') - TO_DATE('[TE.P.PS_PLANNED_START_DT]','YYYY-MM-DD HH24:MI:SS')) from sys.dual

2 REPLIES
Semenov Alexand
Esteemed Contributor

Re: Issue with Date Validation...(subtracting).

Hi,David.
See help in attachment.
Good luck
Cris Robin
Regular Collector

Re: Issue with Date Validation...(subtracting).

You also need to add a decode to this because it will give you an error if one of them is Null.

try this:
SELECT d.duration, d.duration, DECODE(s.status, 'X', 'UNPLANNED', 'PLANNED' ), DECODE(s.status, 'X', 'Unplanned', 'Planned' ) FROM( SELECT DECODE( '[REQD.P.P_START_DATE]', '', '', DECODE( '[REQD.P.P_END_DATE]', '', '', TO_DATE('[REQD.P.P_END_DATE]', 'YYYY-MM-DD HH24:MI:SS') - TO_DATE('[REQD.P.P_START_DATE]', 'YYYY-MM-DD HH24:MI:SS'))) duration FROM dual ) d, ( SELECT 'X' status, duration FROM ( SELECT DECODE( '[REQD.P.P_START_DATE]', '', '', DECODE( '[REQD.P.P_END_DATE]', '', '', TO_DATE('[REQD.P.P_END_DATE]', 'YYYY-MM-DD HH24:MI:SS') - TO_DATE('[REQD.P.P_START_DATE]', 'YYYY-MM-DD HH24:MI:SS'))) duration FROM dual ) WHERE duration < 3 ) s WHERE s.duration(+) = d.duration

it is alittle bit more then you asked for but i hope you will extract what you need from that.

Sagi
//Add this to "OnDomLoad" event