Page 1 of 1
Conversion of Timestamp field
Posted: Wed Oct 20, 2010 12:28 am
by 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.
Posted: Wed Oct 20, 2010 1:02 am
by 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).
Posted: Thu Oct 21, 2010 10:46 pm
by 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"
Posted: Thu Oct 21, 2010 11:30 pm
by 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
Posted: Thu Oct 21, 2010 11:50 pm
by chulett
In other words, dump the "meta SQL" and do it all "in job".
