Datastage Server Job V8.5 OracleDB Date Not Loading Properly

oracledba · Post by **oracledba** » Wed Oct 30, 2013 9:34 am

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?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 30, 2013 2:22 pm

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'.

oracledba · Post by **oracledba** » Wed Oct 30, 2013 3:41 pm

any other suggestions anybody????

ray.wurlod · Post by **ray.wurlod** » Wed Oct 30, 2013 5:51 pm

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.