Conversion of Timestamp field

[DS_SUPPORT]

Hi,

can any one tell us how to covert a timestamp field which contains microseconds in it.

My input is coming as :25-MAY-10 01.19.11.968986 PM

we want Output in the format of 2010-05-25 13:19:11.968986

How to handle micro seconds conversion.

[ray.wurlod]

1. Convert to String.
2. Decompose into components (date, time). Use Field() function.
3. Convert date format. Oconv(Iconv()) with appropriate format strings.
4. Append space and time component (including fractional seconds).

[DS_SUPPORT]

Hi Ray,

For my Target i am using DRS stage,so i am using the meta SQL %DateTimeIn() ,which doesn't handle the microseconds part.

So we tried like %DateTimeIn()%Concat(Microseconds),but we got the error as "ORA-01849: hour must be between 1 and 12"

[ray.wurlod]

Code: Select all

svDatePart::  Oconv(Iconv(Field(InLink.TheTS, " ", 1, 1), "DMY"), "D-YMD[4,2,2]")

Code: Select all

svTimePart:: Oconv(Iconv(Field(Field(InLink.TheTS, " ", 2, 1), ".", 1, 1) : Field(InLink.TheTS, " ", 3, 1), "MT", "MTS")

Code: Select all

svFractionalSeconds::  Field(Field(InLink.TheTS, " ", 2, 1), ".", 2, 1)

Code: Select all

Result::  svDatePart : " " : svTimePart : "." : svFractionalSeconds

[chulett]

In other words, dump the "meta SQL" and do it all "in job".