ksc_store or ??? (301 Views)
Reply
Occasional Advisor
cABany
Posts: 15
Registered: ‎04-30-2012
Message 1 of 4 (301 Views)

ksc_store or ???

Hello,

 

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

 

Thanks!

 

Frequent Visitor
ldw969
Posts: 2
Registered: ‎07-16-2013
Message 2 of 4 (253 Views)

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

Honored Contributor
Utkarsh_Mishra
Posts: 713
Registered: ‎07-13-2010
Message 3 of 4 (242 Views)

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

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Honored Contributor
Jason Nichols KY
Posts: 404
Registered: ‎05-06-2008
Message 4 of 4 (236 Views)

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"
ksc_store REQD.P.P_DATE_FIELD_TOKEN=[SQL_OUTPUT]
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.