date conversion problem

[vikind]

hi,
I have a flat file source with a field recd_dt of type
string now i tried to convert it to date but it gives me
the following error when i run my workflow. the target field to recd_dt in oracle is of date(19) type.

my To_DATE function looks like
to_date(recd_dt, 'YYYY-MM-DD HH24:MI:SS')

this is the error

TE_7007 [<> [TO_DATE]: invalid string for converting to Date
... t:TO_DATE(s:'2004081714513760',s:'YYYY-MM-DD HH24:MI:SS')]

what i observed is the recd_dt field from source has a length of 16 rather than 14..this could be coz of milliseconds ..

would appreciate ur help!

 

[blom0344]

Use SUBSTR function to get the first 14 positions out of the string and then reformat the string untill it has the proper format for conversion to date :

Use SUBSTR function and concatenate || to rebuild to proper format. Look for examples in designer help on TO_DATE

Ties Blom
Information analyst
tbl@shimano-eu.com