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.
String varchar yyyymmdd to Oracle date error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.