ORA-01841: (full) year must be between -4713 and +9999

ady · Post by **ady** » Mon Nov 12, 2007 2:44 pm

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.

DSguru2B · Post by **DSguru2B** » Mon Nov 12, 2007 2:57 pm

Maybe the incoming data does not have a valid year. Re-direct the output to a flatfile and investigte the dates.

chulett · Post by **chulett** » Mon Nov 12, 2007 2:58 pm

You shouldn't use internal dates with the OCI stage. It may 'seem' to work but I seriously doubt it is. Stick with your external representation and let the normal TO_DATE() function handle the coversion for you in the generated SQL.

ady · Post by **ady** » Mon Nov 12, 2007 3:13 pm

@ 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 ?

DSguru2B · Post by **DSguru2B** » Mon Nov 12, 2007 3:16 pm

What is your source? Is it OCI too? Is that a date field or a timestamp field in your source?

ady · Post by **ady** » Mon Nov 12, 2007 3:53 pm

Yes the Source is OCI and the datatype is "DATE"

DSguru2B · Post by **DSguru2B** » Mon Nov 12, 2007 4:13 pm

If its date and your source is OCI, dont you get your dates in internal format? Then you do not need to perform any ICONV() on it. Load the data to a flat file and see weather the date is in internal format or not.

ady · Post by **ady** » Mon Nov 12, 2007 5:43 pm

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.

chulett · Post by **chulett** » Mon Nov 12, 2007 5:45 pm

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.

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.

DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?

Heck no.

DSguru2B · Post by **DSguru2B** » Tue Nov 13, 2007 8:52 am

chulett wrote:
DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?
Heck no.

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.
If you just hit view data, you will see both internal and external formated dates.

chulett · Post by **chulett** » Tue Nov 13, 2007 9:06 am

I deal with Oracle every day and this is just... wrong. Sorry.

DSguru2B · Post by **DSguru2B** » Tue Nov 13, 2007 9:19 am

Then I propose the same, stick to external formats. I cannot check at my end as I do not have oracle at my end.

ady · Post by **ady** » Tue Nov 13, 2007 9:24 am

DSGuru2B wrote:If its date and your source is OCI, dont you get your dates in internal format?

Heck no.[/quote]

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.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 13, 2007 3:52 pm

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

ady · Post by **ady** » Wed Nov 14, 2007 10:19 am

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.