populating date column to table

[dr46014]

i am getting date fields in the file as 12252001
i want the field to load into oracle table
i am using timestamp() and iconv/oconv to insert into oracle table.
but i am getting null values when it is inserted.
how to resolve this issue

[thebird]

i am getting date fields in the file as 12252001
i want the field to load into oracle table
i am using timestamp() and iconv/oconv to insert into oracle table.
but i am getting null values when it is inserted.
how to resolve this issue

You will need to handle the nulls before they are converted into the appropriate date format. Use the Null Handling function provided to check if the incoming value is a null and if so convert or handle the null as you want them in your target. Else (i.e, if incoming value is not null) then go ahead with the iconv/oconv conversion.

[dr46014]

the incoming value in my file are not null.
i think there is some problem with the timestamp part

[DSguru2B]

Depends upon whats the oracle date field. Is it just date or timestamp
If its date use this

Code: Select all

OCONV(ICONV(in.Col,"DMDY[2,2,4]"),"D-YMD[4,2,2]")

If its timestamp then use this

Code: Select all

OCONV(ICONV(in.Col,"DMDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"

[dr46014]

but when i map the date coulmn in the transformer stage by default its taking time stamp.i was using the same code you suggested but instead of concatinating 00:00:00 i was using the timestamp(code...)

[DSguru2B]

TIMESTAMP() converts Internal Date format to Timestamp format (YYYY-MM-DD 00:00:00). You were not providing it internal format. If you really want to use TIMESTAMP() then do this

Code: Select all

TIMESTAMP(ICONV(in.Col,"DMDY[2,2,4]"))