Date Conversion Fix

[meridian99]

I use the following fix to add the century values to a 2 digit year into a 4 digit year. I found that dates < 1950 are converted into 2048 instead of 1948.

update employee_details
set date_of_birth =
to_char(to_date(date_of_birth,'mm/dd/rr'),'mm/dd/yyyy');
commit;

Is there a number of years limit in using the 'rr' function? Using oracle 8i for windows2000.

many thanks

 

[SantaMufasa]

Meridian,

The behaviour of the "rr" mask is to apply the century which is "nearer" to the two digit year. The nearer century prefix for two-digit years "00-49" is "20"; for years "50-99", prefix "19".

In your case, where you legally cannot have any 5-year-old or younger employees, nor have you any 104-year-olds, you have a good, consistent solution available. Try this alternative coding:

update employee_details
set date_of_birth = 
substr(date_of_birth,1,6)||'19'||substr(date_of_birth,7,2);

I recommend, however, that you convert your "date_of_birth" column (and any other character-based date columns in your database) from "varchar" datatypes to "date" datatypes. I cannot think of a good business reason to leave them character-based. Oracle has just too many wonderful built-in algorithms for handling date expressions.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:10 (06Jan05) UTC (aka "GMT" and "Zulu"),
@ 14:10 (06Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.