date conversion

dnat · Post by **dnat** » Mon Aug 11, 2008 1:57 am

Hi,

I have the following date value in the input file 07/31/2008 10:00:34
When i try to load it into the oracle table it is giving me insert error. what is the format to convert using iconv and oconv.

Thanks!

ArndW · Post by **ArndW** » Mon Aug 11, 2008 2:08 am

What stage are you using and what is your Oracle instance's default date format?

dnat · Post by **dnat** » Mon Aug 11, 2008 2:48 am

i am using the transformer stage.

And the oracle's default date format is

SQL> select sysdate from dual;

SYSDATE
---------
11-AUG-08

dnat · Post by **dnat** » Mon Aug 11, 2008 2:51 am

i tried to convert the input format to yyyy-mm-dd hh24:mi:ss using substring in transformer stage and it worked. but i think substring might take longer time than iconv-oconv. so i am trying to convert using iconv.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 11, 2008 4:01 am

You're going to need substring, because you have to convert your date and time separately. In your case the time component does not require conversion so may simply be appended to the converted date.

Code: Select all

Oconv(Iconv(Field(InLink.TheTimestamp," ",1,1),"DMDY"),"D-YMD[4,2,2]") : " " : Field(InLink.TheTimestamp," ",2,1)

That said, Iconv()/Oconv() is a safer approach because it automatically handles single-digit month and day values (if specified as above and the source date has delimiters).