Update year alone in date (Oracle 11g)

date formatoracle-11g-r2update

When I checked one of my client's database, I found some mistakes which will make problems.
The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the result is 01-01-0001. How can I get rid of this problem. Is it possible to update the year alone in these dates.

I can find the dates with this problem by using this query

select from_date from date_table where length(extract(year from from_date))='2';

Can I update this using the logic that

  • Find the dates using the above query

  • Update the year by checking the condition that if year is between '00' and '13' prefix '20' with the year else prefix '19'

How can I do this? Can I do it using Oracle query or should I write code for doing so?

Database is Oracle 11g R2 working in RHEL5.

Best Answer

You can add a number of days to from_date like this:

UPDATE table_date
SET from_date                              = from_date + 730487
WHERE LENGTH(extract(YEAR FROM from_date)) = '2'
AND from_date                             <= to_date('01/01/0014','dd/mm/yyyy')

UPDATE table_date
SET from_date                              = from_date + 693962
WHERE LENGTH(extract(YEAR FROM from_date)) = '2'
AND from_date                             > to_date('01/01/0014','dd/mm/yyyy')
Related Question