Page 1 of 1
Question about system timestamp in oracle table
Posted: Mon Nov 27, 2006 10:15 am
by 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
Posted: Mon Nov 27, 2006 10:29 am
by 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.
Posted: Mon Nov 27, 2006 10:32 am
by 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.
Posted: Mon Nov 27, 2006 1:43 pm
by 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