Project and Portfolio Management Practitioners Forum

ksc_store or ???

Trusted Contributor.

ksc_store or ???



I have a date field I want to update passing through an execution step...


when setting a date I've used [SYS.ITG_TIME_STAMP]


However, I need update a field to [SYS.ITG_TIME_STAMP] + 7 days


Based on my failed attempts, it seems like I can't perform an operation on [SYS.ITG_TIME_STAMP].


So what can I do?


I've tried using a "on field change" rule on the request type


select TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY'),'Month DD, YYYY'), TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY'),'Month DD, YYYY') from dual


This didn't work either.


Please Help





Re: ksc_store or ???

You need to ensure that the date format is specified correctly, including the time format. For example,

select TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY HH24:MI:SS'),'Month DD, YYYY'),
TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY HH24:MI:SS'),'Month DD, YYYY') from dual

Acclaimed Contributor.

Re: ksc_store or ???


Try this


SELECT TO_DATE(substr(sysdate+7,1,10),'yyyy-mm-dd'), TO_DATE(substr(sysdate+7,1,10),'yyyy-mm-dd') from dual


even if you don't use TO_DATE then also it will work fine in RULE.


SELECT sysdate+7, sysdate+7 from dual

Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Jason Nichols K
Acclaimed Contributor.

Re: ksc_store or ???

While the suggestions here are mostly valid, what everyone seems to be missing is that the request is to be able to store a date into a PPM field. For those that aren't familiar with, or have just forgotten, all PPM parameter fields are VARCHAR types, so the TO_DATE function should NEVER be the outer most function in a select statement that will be fed into ksc_store. It must be in a character format, and more specifically, PPM stores date values in the database with this format string: 'YYYY-MM-DD HH24:MI:SS'. If it's not in that format, PPM will not allow it to be stored into a date field as it would violate the validation of the field.

I've done many such date manipulations in the 12.5 years I've worked with PPM/ITG/Kintana. Here is what I would do:

ksc_itg_run_sql QUERY_STRING="SELECT to_char(SYSDATE + 7, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL"