TimeStamp in oracle

[muascdev]

I need to update a timestamp filed when we update a record, ad we use oracle 9i. in th transformer there is TimeDate() function whic is in this format 'HH24:MI:SS DD MON YYYY' and when the sql is genarated its in this format 'YYYY-MM-DD hh24:mi:ss'. how do i convert the time stamp in transformer into this format. thanks

[kris007]

Look at the ICONV and OCONV functions. They should help you converting what you need. Also check the function DSJOBStartTimeStamp if you need current timestamp

[ray.wurlod]

Do you need the system time "now", or the system time when the job started?

If the former, you could use SYSDATE as the default value for the column and not insert it from the DataStage job. Or

Code: Select all

Oconv(Date(), "D-YMD[4,2,2]") : " " : Oconv(Time(), "MTS:")

If the latter, the DSJobStartTimestamp macro delivers the correct format. Or substitute @DATE for Date() and @TIME for Time() in the above expression.