String varchar yyyymmdd to Oracle date error

[nvalia]

Hi,

I am reading from a file with one column DT1 (varchar) having date values '20070802'. I am trying to load this in an Oracle table column AS_OF_DATE( timestamp), after using the following conversion code OCONV(ICONV(DT1, "DYMD[4,Z,Z]"),"D-YMD[4,2,2]") : " 00:00:00".

It does't throw any error but the jobs aborts. It lists a log message as
"insTdwFxRateCurrency..TRN_TypeConversion: The value of the row is: AS_OF_DATE = 2007-08-06 00:00:00 SOURCE_SYSTEM_ID = IBS SITE = BOS BRANCH = 01 CURR_CODE = ABC CURR_DESC = TEST CURRENCY SPOT_RATE_IND = M SPOT_RATE = 1 MARKET_NOSTRO_POS = 0"

I have checked all other target cols, they seem to be Ok.
what is going wrong here.

[ray.wurlod]

You don't seem to have any quote characters in the row, where the data type is obviously string. This may be an artifact of how the error is reported. You might also try [4,2,2] rather than [4,Z,Z] in the Oconv() function.

[snt_ds]

Ray,

I tried using 4,2,2 in OCONV, but it still throws the same error message.
Anything else I can try?
Infact I aslo tried using the same code via a Routine.

Also I did not understand the meaning of your first statement regards Quote..

[ray.wurlod]

I would have expected to see CURR_CODE = 'ABC', for example, not CURR_CODE = ABC.

Essentially you need to format the date precisely how Oracle's date picture is set up, or explicitly to specify TO_DATE with an alternate date picture.

[chulett]

Unless the target field is actually some form of a TIMESTAMP rather than a DATE field, which seemed to have been implied in the original post.