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