Time Conversion Problem - Timezone?

[evee1]

I have an Oracle connector stage with a Date(26,6) field that gets assigned with the result of to_date('20111023170825', 'yyyymmddHH24MISS').

Then in a transformer I convert it to VarChar(50) using the following

Code: Select all

Oconv(lnk_inp.D_DATA_STORE_DATE,'MTS')

and store into the file.
But the value in a file is 04:26:42, rather than 17:08:25.

Am I using the wrong time format? Is there some timezone indicator I should use (however I cannot find much information about time zones in DS)?

[ray.wurlod]

Oconv() with MTS will only work on the time component. Yours is converting the entire number as if it were an interval then reporting it as a time of day. But what you're wanting to do is formatting rather than converting, so you should prefer the Fmt() function with a mask. Otherwise you have to break the source timestamp into date, time and fractional seconds then convert each of the first two appropriately before re-assembling.

Try this instead:

Code: Select all

Fmt(lnk_inp.D_DATA_STORE_DATE, "L####-##-## ##:##:##")

[evee1]

Hmm, now I'm getting the following string in the file
"1600-2 - : : ". It seems just to format internal representation of the date, which is 16002.

[ray.wurlod]

So the date IS being converted to internal format. In this case that is not what you want. Can you specify TO_CHAR rather than TO_DATE in the SQL statement?