How to change and convert datatype in ODI for specific field before loading in target table

user12251389

Hallo All,

I am using ODI 12c and quiet new with this tool. I want to truncate and reload the target table from source but getting error as ORA-01861: literal does not match format string.

In my source table SRC_TBL i have column modtime which is of Varchar datatype. And the value in this field is something like '2011-02-25-08:47:43,731984'.

Before i load the data into target table TRG_TBL, i want to convert this modtime field to date and use below condition such that the value store in the TRG_TBL for field modtime will be '2011-02-25 08:47:43'

to_date(SUBSTR(modtime, 1, 19), 'YYYY-MM-DD-HH24:MI:SS') AS modtime

Can someone please help how to change this datatype in ODI and use above condition for field modtime. I dont want to use option CUSTOME_TEMPLATE in ODI where we have to write select query and override the logic of loading.

Below is my simple mapping:

Marco Fris

You can simply paste your expression (without the AS modtime) in the target field. Then open the expression editor and replace the 'modtime' with the correct attribute from the left pane from the expression editor.

Marco Fris

You can simply paste your expression (without the AS modtime) in the target field. Then open the expression editor and replace the 'modtime' with the correct attribute from the left pane from the expression editor.