Concatenate/Convert date and time to timestamp

vinaymanchinila · Post by **vinaymanchinila** » Tue Aug 15, 2006 8:14 am

Hi,
I have 2 source columns
Column1 (Char8)=20060624
Column2(Char6)=174911

I need to write to an oracle table as a timestamp, is there a function I can use in Modify stage to either concatenate or convert the source columns to single timestamp output column?

Thanks,

kris007 · Post by **kris007** » Tue Aug 15, 2006 9:45 am

What happens when you use StringtoDate and StringtoTime functions? Does it help?

ray.wurlod · Post by **ray.wurlod** » Tue Aug 15, 2006 10:45 pm

After date_from_string() and time_from_string() in one Modify stage you will need a second Modify stage to apply timestamp_from_date(). Two adjacent Modify stages are not permitted so you will need a Copy stage between them.

vinaymanchinila · Post by **vinaymanchinila** » Wed Aug 16, 2006 7:29 am

Instead of two modify stages and one copy satge , is it not better to use one transformer?

In the transformer, the below function works with dummy time

StringToTimestamp(To.SrcDate: " 000000","%yyyy%mm%dd %hh%nn%ss")

but when I replace the " 000000" with a column it gives me *********

StringToTimestamp(To.SrcDate: To.SrcTime,"%yyyy%mm%dd %hh%nn%ss")

The SrcTime has values like 124643,130216,125553.

Thanks for the help.

vinaymanchinila · Post by **vinaymanchinila** » Wed Aug 16, 2006 8:22 am

Hi,

I have got it working with the below format in a transformer, I was missing the space between the two columns when I concatenate them.

StringToTimestamp(To.SrcDate:' ':To.SrcTime,"%yyyy%mm%dd %hh%nn%ss")

Old format was

StringToTimestamp(To.SrcDate:To.SrcTime,"%yyyy%mm%dd %hh%nn%
ss")

Now will see if I can get it working in one Modify stage.
Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Aug 17, 2006 8:53 am

You will not be able to get it to work in one Modify stage for reasons already stated. The Modify stage does not support nested functions.