Wednesday 26 February 2014

Date Issue in Oracle YY vs RR format

During my last data migration for customer banks we found an issue in the data post migration. The Customer Bank accounts got created with an incorrect start date.
For Eg : if the start date was 1-Jan-1970 , the bank account got  created with a start date 1-Jan-2070.

The reason for this was the date format being used :

We had used : 
TO_DATE ('01-JAN-70', 'DD/MON/YY') -->  this converts the date to 1-Jan-2070

The correct way to include the dates will be to use the typecast : TO_DATE ('01-JAN-70', 'DD/MON/RR')
 The RR format works as expected. For the dates between 00-49 , Oracle will translate them to year 2000 to 2049. for the values 50-99 Oracle will translate them to 1950 to 1999.


Depending on the business need the respective typecast can be used.

The basic difference between the YY and RR date formats is as  below :
In 'YY' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx00 and ending with the next xx99.
In 'RR' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx50 and ending with the next xx49. That is, the window of possible dates is shifted by 50 years.


No comments:

Post a Comment