Date format not supported
Moderators: chulett, rschirm, roy
Date format not supported
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hi friendSainath.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. ...
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hello there I can use IConv and Oconv but there are loads and loads of date fields it kills the performance.Sainath.Srinivasan wrote:Search for IConv and OConv. ...
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
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
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
Hi there.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. ...
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"
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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