Conversion from varchar data type to date with timestamp

nilesh · Post by **nilesh** » Tue Aug 21, 2007 12:17 am

Hi,

I am using CSV file as my input stage with date data type defined as varchar2(20) . The format is date with timestamp.My target data type is date. I also want to preserve its timestamp. My target database is oracle10g.

Can anybody help me out how do i make conversion from varchar to date in transformer.

Thanks in advance
Nilesh

Maveric · Post by **Maveric** » Tue Aug 21, 2007 12:23 am

StringToTimestamp(). and then DateFromTimestamp().

JoshGeorge · Post by **JoshGeorge** » Tue Aug 21, 2007 12:47 am

As there are no data types within DataStage (Server Edition), if your source date is in valid timestamp format, you can load it straight in to a column whose data type is TimeStamp. If your source data character string is not in valid format, then use substring and concatenation to get it into a valid format.

Maveric wrote:StringToTimestamp(). and then DateFromTimestamp().

If this is a server job, I think above functions won't work.

ArndW · Post by **ArndW** » Tue Aug 21, 2007 12:52 am

Those functions won't work in Server, as already stated. If your string date format is the same as the default date format of your Oracle instance then you don't need any conversion at all. What stage are you using to write to Oracle?

ray.wurlod · Post by **ray.wurlod** » Tue Aug 21, 2007 1:51 am

In what format is the date/time in the CSV file? If it is correct for your Oracle settings, then (as others have observed) you need to do nothing. Otherwise you can re-arrange the string components with substring and concatenation operators.