Update year alone in date (Oracle 11g)

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.

I would recommand always to use the appropriate datatypes, operators and functions and explicit conversion and not to use string literals and string processing and implicit conversion.

Look at your statement

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

The return type of the length statement is clearly a number. So why do you compare it with a string literal? Something has to be converted implicitly. The number result of the length function to a string or the string literal '2' to a number. Do you know which one is converted? Do you know if there can any problems arise from this conversion? Is there a special reason why you take a string constant instead a number constant?

As far as I know the string constant will be converted to a number and I can't see any problems with this conversion. I also think that there is no special reason why you take a string constant instead of a number constant. But why bother about that questions?

Use a number literal and write

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

I think you want to find all rows where from_date by the implicit conversion to a very early date and therefore is before 1st January 100. So why string processing? Use date processing and write

select from_date 
from date_table 
where from_date<to_date('0100-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

If there is an index on the from_date column this query may be able to use the index.

This query will also find rows with a from_date before '0010-01-01 00:00:00' and even dates before '0000-00-00 00:00:00'. Also it can be easily adjusted to other timestamps. But if you want to explicitly use the year ( and you are sure that there are no dates before 0 B.C.) you can

select from_date 
from date_table 
where extract(year from from_date) between 0 and 99
/

If there is an index on the from_date column this query may not be able to use the index.

Adding 2000 years to a date can be done by adding INTERVAL '2000' YEAR(4)

UPDATE table_date
SET from_date  = from_date+INTERVAL '2000' YEAR(4)
WHERE to_date =to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

Because you started with string processing by inserting rows with the date represented as strings you can also make your correction using string processing. If you did not use a time part when inserting the rows you could/should omit the HH24:MI:SS format part in the following statements

UPDATE table_date
SET from_date  = to_date('20'||substring(to_char(from_date,'YYYY-MM-DD HH24:MI:SS'),2),'YYYY-MM-DD HH24:MI:SS')
WHERE to_date =to_date('0014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
/

StackExchange Database Administrators Q#48575, answer score: 6