Date format not supported

karry450 · Post by **karry450** » Thu Jul 16, 2009 1:36 am

Hi friends,

I have migrated some jobs from DS 7.5.2 to DS8.1

These job extract data from oracle and write to flat file

But my job is having some date fields

ex
case 1) input(timestamp) : 02/02/2009 00:00:00
output(timestamp) : 02/02/2009 00:00:00 this is successfull
case 2) input (timestamp): 02/02/2009 my job fails

case 3) input(date):02/02/2009 output(date):14977 this is successfull but the output is not correct

In the above example the third output is not the correct output

can any one help me if I have date format in the form like 02/02/2009 to get the correct output

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 16, 2009 2:10 am

In case 2, input is date and not timestamp. So it fails.

In case 3, the output appears to be julian date.

Can you explain what you expect to see.

karry450 · Post by **karry450** » Thu Jul 16, 2009 2:17 am

Sainath.Srinivasan wrote:In case 2, input is date and not timestamp. So it fails.

In case 3, the output appears to be julian date.

Can you explain what you expect to see. ...

Hi friend

My oracle data is like this 02/02/2009 and I want to see the same in output 02/02/2009 what datatype shall I use in oracle stage and what data type shall i use in flatfile stage

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 16, 2009 2:20 am

Search for IConv and OConv.

karry450 · Post by **karry450** » Thu Jul 16, 2009 2:30 am

Sainath.Srinivasan wrote:Search for IConv and OConv. ...

Hello there I can use IConv and Oconv but there are loads and loads of date fields it kills the performance.

I want the data*(02/02/2009) from oracle to be written to flat file and then from flat file to oracle.

when I am writing from oracle Im using date datatype in both oracle and flatfile stage but the flat file output is 14977 but when Im writing the same flatfiles data to oracle again the rows are not getting insert because flat files data is not in date format.

can any one help me

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 16, 2009 2:48 am

Are you using user-defined SQLs ? That may be one of the reasons for translation. Pre-defined SQL tend to have To_Char() around the date values.

Did you try mapping the values from flat-file to Oracle? DataStage automatically translates (it's internal) julian dates to Date of corresponding dbms.

If you want to do same derivation for multiple columns in a single go, you can do "derivation substitution" (I assume that is the correct technical name). Search for the term.

karry450 · Post by **karry450** » Fri Jul 17, 2009 4:41 am

Hi There,

My source data is in the format of DD/MM/YYYY

Im writing ORACLE to FLAT FILE and then in the next job FLAT FILE to ORACLE(BULK LOADER)

my examples are like this

ORACLE FLATFILE FLATFILE ORACLE(BULKLOADER)ERROR
02/02/2009 14977 14977
DATE DATE DATE literal does not match format string
In the above example oracle bulk loader is throwing error message "literal does not match format string" so cannot insert data

ORACLE
02/02/2009
TIMESTAMP
When I use timestamp My job fails saying "date format not recognized"

can any one tell me how to handle this when BULK loader is used

Thanks

chulett · Post by **chulett** » Fri Jul 17, 2009 6:26 am

Use a TO_CHAR() on the select with a mask of 'YYYY-MM-DD HH24:MI:SS' and use a Datatype of Timestamp in the job all the way through.

karry450 · Post by **karry450** » Fri Jul 17, 2009 7:32 am

chulett wrote:Use a TO_CHAR() on the select with a mask of 'YYYY-MM-DD HH24:MI:SS' and use a Datatype of Timestamp in the job all the way through. ...

Hi there.

When I say timestamp it is already TO_CHAR
why need of TO_CHAR I also tried TO_TIMETSAMP but then the job gets success but my error file says

"ORA-26041: DATETIME/INTERVAL datatype conversion error"

chulett · Post by **chulett** » Fri Jul 17, 2009 8:34 am

If the stage generates the SQL then yes, it will already use TO_CHAR. And you need it, one way or the other, to make this work properly. Lastly you can't use TO_TIMESTAMP as you're not dealing with an Oracle TIMESTAMP column, but rather a DATE - which just so happens to carry a time portion in Oracle and thus is best handled as a Timestamp in any job.

karry450 · Post by **karry450** » Sun Jul 19, 2009 10:59 pm

Hi There,

I have a DATE with format of DD/MM/YYYY HH24:MM:SS I want to move this from ORACLE to FLAT FILE and then to FLAT FILE to ORACLE(BULK LOADER) can anyone help me what data type( either timestamp or date) shoulb be used through out the job..

I am getting this error ORA-26041: when Im using timetsamp as datatype through out the jobs.

Can anyone help me please.

Thanks

chulett · Post by **chulett** » Mon Jul 20, 2009 6:16 am

I've already given you the answer, not sure why you'd choose to ignore that. That is not the format I suggested you use, hence your error.