Date format not recognized after upgrade to 8.5

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
vinod_sri
Premium Member
Premium Member
Posts: 18
Joined: Thu Nov 15, 2007 2:11 pm

Date format not recognized after upgrade to 8.5

Post by vinod_sri »

Hello All,
sanoojk wrote:I am using the ORAOCI9 stage for accessing the oracle database in the DS 7.5. In one of the jobs, I have the extraction query :
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual . This is the auto generated query in the oracle stage when defined the column name as sysdate, data type as timestamp and table name as dual.
When I used the same job in DS 8.5 its showing the error: 'date format not recognized'. Is this due to any set up issue during the DS 8.5 server installation or DS 8.5 will not support this conversion?
Did this issue get resolved? I am experiencing the similar issue:

We have migrated the DataStage Server job from 7.5.3 to 8.5 which is using Oracle OCI9 stage, Transformer and the target as hash file.
It is straight pull from Oracle table which has 4 Date fileds in the table which are defined "Date" as datatype. But in the OracleOCI Stage, All the 4 fileds are defind as "TimeStamp" and used the same function mentioned above for the four Date fields.

And, the Select SQL in OCI Stage is "Auto Generated".

When i try to view data, It is throwing the warning. When i removed the TO_CHAR() function and defined the datatype as "Date", i am able to view the data in the "mm-dd-yy" format. But when i run the job, it has parsed Julian date in the hashed file.

We have more than 1000 jobs where we have similar issue.

We created a sample job using Oracle Connector stage and its working fine. But replacing OCI Stage with Oracle Connector in more than 1000 jobs is a massive task.

I was wondering IBM would have some fix for this and a work around.

I am planning to contact IBM concierge team on this matter.

And, I didn't find the link to download "Job Migration Tool" in IBM website.

Any ideas/advice or experiences is highly appreciated.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

<Split out to your own post>
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply