Question about system timestamp in oracle table

[ady]

I am trying to get the system date and time into a oracle table. I am using Oconv(Date(),"D,YMD[4,2,2]") and giving the datatype as Timestamp in datastage.

I get the date in the oracle table but the system time displays as 00:00:00. Do i have to give it another format to get the time . Please help

[chulett]

Yes... all you've handled is the Date part. You'd need to either add in a Time() function call or switch to the combo one - TimeDate? We use that one in a custom routine to get a System timestamp.

[ArndW]

DataStage "DATE" conversions only cover the date portion and not the time. You will need to specify

Code: Select all

OCONV(DATE(),'D4YMD[4,2,2]'):' ':OCONV(TIME(),'MTS')

. That will give you the time accurate to the nearest second without milliseconds.

[ray.wurlod]

The DATE() and TIME() functions refer to the system clock; they will increment over the run time of your job.
To get the job start time you can prefer the system variables @DATE and @TIME in such an expression.

Code: Select all

Oconv(@DATE, "D-YMD[4,2,2]") : " " : Oconv(@TIME, "MTS:")

Or you can simply use the DataStage macro DSJobStartTimestamp