How to read timestamp in "2004-01-01 12:12:12.112"

akash_nitj · Post by **akash_nitj** » Thu Dec 02, 2004 5:25 am

Hi
How can i read the timestamp in the following format in Parallel job .

Format of timestamp is "2004-01-01 12:12:12.112"

TIA
Akash[/b]

ArndW · Post by **ArndW** » Thu Dec 02, 2004 6:11 am

Akash,

if you want to convert this string into a DS internal format then split it into 3 distinct values: Date, Time and Milliseconds

Say "X" is your string "2004-01-01 12:12:12.112",

Then DateVal = ICONV(FIELD(X," ",1),"D4YMD")
TimeVal = ICONV(FIELD(FIELD(X," ",2),".",1),"MTS")
MilliVal = FIELD(X,".",2)

The internal time representation is in integer seconds since midnight and the ICONV and OCONV time conversions will strip out the milliseconds, so you have to add them manually (ICONV'ing a timestring with decimal digits will cause the function to round up by one second).

ray.wurlod · Post by **ray.wurlod** » Thu Dec 02, 2004 3:24 pm

Arnd's advice only works in the BASIC Transformer stage. If you are using the regular parallel Transformer stage, the answer will depend on where you're reading the timestamp from. If it's a text file you can decompose it using substring techniques. If it's from a database you may also need to perform some kind of CAST operation.

ArndW · Post by **ArndW** » Thu Dec 02, 2004 3:31 pm

ray.wurlod wrote:Arnd's advice only works in the BASIC Transformer stage...

Oops - just noticed that I responded in the incorrect sub-forum

T42 · Post by **T42** » Fri Dec 03, 2004 12:01 pm

You could read in microseconds for timestamps in PX. You just need to enable the option within your column definition. (Sql Type needs to be Timestamp, and Extended needs to be Microsecond)

venkateshkumarg · Post by **venkateshkumarg** » Mon Dec 20, 2004 9:15 pm

Hi,
recently we posted some work around...search for Err insert timestamp value (with Microseconds) into Oracle9i

chulett · Post by **chulett** » Tue Dec 21, 2004 12:08 am

Or just post a direct link to the thread in question.