how to convert format timestamp

pxraja · Post by **pxraja** » Sat Sep 06, 2008 9:33 am

Hi All,

how to convert timestamp format

Inlink.Field1
2008-09-06 20.30.156546

Outlink.Field1
2008-09-06 08:30:15 PM

I had tried using Iconv and Oconv like..

Oconv(Iconv(Inlink.Field1,'D-YMD[4,2,2] MTS.'),'D-MYD[4,2,2] MTHS')

the above derivation not returning any values to the Outlink.Field1

Any suggestions are most welcome

Thanks in advance

chulett · Post by **chulett** » Sat Sep 06, 2008 9:49 am

You have to split the timestamp into date and time, convert them separately and then cat them back together. For you, all you need to handle is the time portion since the date isn't be reformatted.

pxraja · Post by **pxraja** » Sat Sep 06, 2008 10:03 am

Hi chulett,

Thanks for your response,

but I noticed that while executing query from a oracle its displaying like
Inlink.Field1
2008-09-06-20.30.355465

what I had posted is not the one to be converted.

I could not able to make a query in oracle itself. But while running the datastage job and populating into oracle table having the datatype timestamp. Its output is like

Outlink.Field1
2008-09-06 08:30:35 PM

Its converting by itself but I need to define my conversion as

Outlink.Field1
2008-09-06 20:30:35

please any one can light me up

Thanks in advance

chulett · Post by **chulett** » Sat Sep 06, 2008 10:43 am

Same advice applies: Separate, convert, combine. First off, what is your source for this field? If it is also Oracle, then you can save yourself all this work just by selecting it properly in the first place.