ORA-01841: (full) year must be between -4713 and +9999
Moderators: chulett, rschirm, roy
ORA-01841: (full) year must be between -4713 and +9999
I am trying to insert DataStage numeric internal date value into a OCI stage "DATE" field.
The transform I am using to convert the original date into internal format is like ICONV("1950-03-02", "D-YMD[4,2,2]"). It seems to work for many of my jobs but I am having a problem with this one job where it gives the error :
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
The date values I am trying to insert are generally like "-6753" , "14245" , "14123" , "13939" , "14092".
Please Help.
The transform I am using to convert the original date into internal format is like ICONV("1950-03-02", "D-YMD[4,2,2]"). It seems to work for many of my jobs but I am having a problem with this one job where it gives the error :
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
The date values I am trying to insert are generally like "-6753" , "14245" , "14123" , "13939" , "14092".
Please Help.
Be nice to nerds. Chances are you’ll end up working for one.
--- Bill Gates
--- Bill Gates
@ DSGuru
The input data looks good, it is like
30-Nov-2004
31-Dec-1998
31-Mar-2004
30-Nov-2004
@Chulett
I myself dont like the job design but I am comparing dates in my job and doing a (internal date - 1) in the job to get the previous day date, And I am "SUPPOSED" to stick to this job design. All the jobs are designed like this so ppl dont like me changing the structure for 1 job.
The ICONV works fine and I even did a OCONV to a seqential file just to check if something is wrong when I am writing the data to OCI. The data looks good after OCONV and gives perfect dates. Any others issues I should look for ?
The input data looks good, it is like
30-Nov-2004
31-Dec-1998
31-Mar-2004
30-Nov-2004
@Chulett
I myself dont like the job design but I am comparing dates in my job and doing a (internal date - 1) in the job to get the previous day date, And I am "SUPPOSED" to stick to this job design. All the jobs are designed like this so ppl dont like me changing the structure for 1 job.
The ICONV works fine and I even did a OCONV to a seqential file just to check if something is wrong when I am writing the data to OCI. The data looks good after OCONV and gives perfect dates. Any others issues I should look for ?
Be nice to nerds. Chances are you’ll end up working for one.
--- Bill Gates
--- Bill Gates
Yes,
The date is in internal format ( i was saying i did a oconv to a text file and made sure that the conversion to internal format is ok)
But my problem is when I write the internal date to a DATE field in a OCI stage it gives me the error posted above. It should convert Internal date to Oracle format but it dosent do it.
The date is in internal format ( i was saying i did a oconv to a text file and made sure that the conversion to internal format is ok)
But my problem is when I write the internal date to a DATE field in a OCI stage it gives me the error posted above. It should convert Internal date to Oracle format but it dosent do it.
Be nice to nerds. Chances are you’ll end up working for one.
--- Bill Gates
--- Bill Gates
That's all well and good. You can certainly turn any date into DataStage internal format for ease of doing date comparisions or date 'math', that's all peachy. The concept that you can send those internal dates to an Oracle DATE field is where I was pushing back. When you get done with your comparisions and have a final date, it needs to be converted back to external format before it goes to OCI. And you need to do the conversion - Oracle won't.ady wrote:I myself dont like the job design but I am comparing dates in my job and doing a (internal date - 1) in the job to get the previous day date, And I am "SUPPOSED" to stick to this job design. All the jobs are designed like this so ppl dont like me changing the structure for 1 job.
Heck no.DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I rememeber, I used to get it in internal format. Do not have Oracle at the current site so cannot test this piece out. If you just do a select on a date field, you will see that it is in internal format. Yes, I found that strange as well.chulett wrote:Heck no.DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?
If you just hit view data, you will see both internal and external formated dates.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Heck no.[/quote]DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?
Hmm ... tht sounds like trouble for me ...
All the jobs i am working with get dates from OCI in Internal Format, let me explain
My source data from the OCI stage is the datatype "DATE"
ex : 31-Dec-2006 (this is the format in the table)
And I am doing a
TO_CHAR(Column, 'YYYY-MM-DD HH24:MI:SS') to get the data from the table and the value I get is like "14276" , "13939" etc.
and when I write this values to a OCI table "DATE" field they get converted as "2-Feb-1999" , "5-May-1969" etc. When I view the data from datastage it still shows me the values as "14276" or "-6530" etc , but when I view it through a SQL Viewer then it shows as "2-Feb-1999" , "5-May-1969" etc.
These job are creted by someone else and I am currently making changes to them. Please let me know if I am doing something wrong.
Be nice to nerds. Chances are you’ll end up working for one.
--- Bill Gates
--- Bill Gates
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"31-Dec-2006" is NOT "the format in the table". Oracle stores dates as a binary value. What you've described is the default date picture at your site.
I can not understand how you can get an integer from the TO_CHAR function you have described - are you sure you have not then applied an Iconv() function to the date portion of that?
Create a job that simply extracts from Oracle and writes to a text file, and tell us what date format is in the text file. Do the same applying your TO_CHAR function and report that result.
After that we'll concentrate on what you need to do in the Transformer stage.
I can not understand how you can get an integer from the TO_CHAR function you have described - are you sure you have not then applied an Iconv() function to the date portion of that?
Create a job that simply extracts from Oracle and writes to a text file, and tell us what date format is in the text file. Do the same applying your TO_CHAR function and report that result.
After that we'll concentrate on what you need to do in the Transformer stage.
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.
Ray,
I meant that the source Orcale table has the dates in that format. When I use the TOCHAR function in the SQl to extract the date I get the dates as 14245 , 13939, 13880 ...etc.
I created a job to extract the dates to a text file and thats what I get in the text file after the TOCHAR function.
I meant that the source Orcale table has the dates in that format. When I use the TOCHAR function in the SQl to extract the date I get the dates as 14245 , 13939, 13880 ...etc.
I created a job to extract the dates to a text file and thats what I get in the text file after the TOCHAR function.
Be nice to nerds. Chances are you’ll end up working for one.
--- Bill Gates
--- Bill Gates