We have dates that are loaded into the datawarehouse from a flat file in the format ‘YYMMDD’ via sql*ldr.
Now even if we change the format mask to RR, we still get only 6 digits of data.
Now here is the scenario.
The date field is a ‘BIRTH_DATE’ field. A person born in 1975, AUGUST,30 will have the date filed in flat file as ‘750830’ and would be converted by oracle as ‘30-AUG-1975’ since the pivot year for Oracle (RR) id ‘49’
But if a person is born in 1948,AUGUST,30…he will have a date in the flat file as ‘480830’ and will be converted by oracle as ‘3–AUG-2048’.
I think the only true solution is to make your database Y2K compliant: years need to be represented in 4 digits. That means changing the date field to 8 digits.
Michael Welter
Sr. Technical Analyst
AirTouch
We have dates that are loaded into the datawarehouse from a flat file in the format ‘YYMMDD’ via
sql*ldr.
Now even if we change the format mask to RR, we still get only 6 digits of data.
Now here is the scenario.
The date field is a ‘BIRTH_DATE’ field. A person born in 1975, AUGUST,30 will have the date filed in
flat file as ‘750830’ and would be converted by oracle as ‘30-AUG-1975’ since the pivot year for
Oracle (RR) id ‘49’
But if a person is born in 1948,AUGUST,30…he will have a date in the flat file as ‘480830’ and will
be converted by oracle as ‘3–AUG-2048’.
What do you mean by …‘make your database Y2K compliant’ ? Its not the database thats giving problems…its the bad data (6 digits) thats coming in. How do we rectify it ?
A database that stores years in two digits only is not Y2K compliant. That one field probably need to be expanded to 8 digits to accomodate a four digit year. Is that possible?
Michael Welter
Sr. Technical Analyst
AirTouch
What do you mean by …‘make your database Y2K compliant’ ? Its not the database thats giving problems…its the bad data (6 digits) thats coming in.
How do we rectify it ?
Database obviously stores date in 4 digits…it the format mask RR that was used for conversion.
Well the 6 digit comes from an outer source which is not in our scope at all.
One possible solution can be to update the table after it has been loaded.
But how do we do that…
even if we say the all dates that are less than 2049 but greater than 1999 must be updated to (YYYY-100).
But this is a temporary fix.
If a person is born in 2001 ( future aspect)…he will be updated to 1901…which we donot want. So we must have an intelligent way to do it. Any suggestions ?
This is probably obvious but
If your date inputs are 2 digit years you will have to write a program that does windowing based on business knowledge about each date you want to load. Transactions dates are normally easy (there are not many systems that have transactions pre 1950’s knocking about). However date of births are harder.
You tell me what a DOB 01/01/00 is more likely to represent a 100 year old or a newly borne ? If it’s hospital admittions, I’d check the dept (Geriatric or Maternity) before choosing.
The SQL Loader you’re using has probaly got a < 50 window default - check the docu to see how to change this.
We have dates that are loaded into the datawarehouse from a flat file in the format ‘YYMMDD’ via sql*ldr. Date field is a ‘BIRTH_DATE’ field.
You really should have a talk with the people creating the load file, it’s not even a Y2K issue, it’s just plain stupid. Some persons do grow older than 100 years nowadays, so if you get a person born in year 99, is that a baby or a person of 101 years? So it’s got nothing to do with Y2K, the problem was there before.
May be you can derive the right answer if you’ve got age in the file as well, otherwise the best you can do is pick a smart year to split on and you do not depend on the oracle (RR) id. This may help in sql*ldr:
We saw the same problem, the fix is pretty easy. The problem is that the oracle server is sending you the date in dd-mon-rr, and your client is not setup to receive it. Using the dd-mon-yy default oracle date format, tells the server to send the date and the current system century, which is now 20. This is why you are seeing 2079 etc. Usually the client does not have the key set, so it defaults to dd-mon-yy and accepts the century from the server.
To fix this problem, set the NLS_DATE_FORMAT key in your client registry to dd-mon-rr. This tells the server to send the century set with the date.
Your Oracle DBA will be able to help you find the place to set the key.