Here is a peculiar error.
In a datastage server job,
My source data from a sequential file has date field with following value
yyyymmdd
Date
20121017
I am loading into oracle DB however that column in the database shows up as '21-JUN-57' (This is 06/21/7057)
I am not doing any transformations on the date. It is straight mapping.
I want the target in format 17-OCT-2012
The source data from sequential file has date as SQL Type and target has the same SQL Type. The Oracle DB matches perfectly in terms of column name, data type.
Can you please shed some light on why the date is not loading correctly and how to resolve the isssue?
Datastage Server Job V8.5 OracleDB Date Not Loading Properly
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Sequential files don't have data types - they just have text. Try including a TO_DATE function in a user-defined SQL statement, specifying a date picture that corresponds with your actual data, that is 'YYYYMMDD'.
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:
Does the Oracle DB match perfectly in terms of default date picture?
(Clearly, from your example, it does not.)
Therefore you either have to transform the string coming in from the sequential file so that it conforms to your Oracle default date picture, or use TO_DATE() function in your INSERT/UPDATE statements to advise Oracle of the format you are actually providing from DataStage.
(Clearly, from your example, it does not.)
Therefore you either have to transform the string coming in from the sequential file so that it conforms to your Oracle default date picture, or use TO_DATE() function in your INSERT/UPDATE statements to advise Oracle of the format you are actually providing from DataStage.
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.