Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
if there are from_date values with a one digit year your query won't find it –  miracle173 Aug 23 '13 at 13:25
add comment

2 Answers

up vote 2 down vote accepted

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')
share|improve this answer
    
Thank you! It worked fine for me. Can you please give an explanation? Why is the to_date 01/01/0014? –  Maximin Aug 23 '13 at 7:44
    
I guess that you have not date > 2014; so, if the from_date is > 2014 it is supposed to be a date from the previous century. So, the number of days to add is different. Note that, depending of the volum, you might have to iterate and commit every X rows. –  Nicolas Durand Aug 23 '13 at 7:55
add comment

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')
/


UPDATE table_date
SET from_date  = from_date+INTERVAL '1900' 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')
/


UPDATE table_date
SET from_date  = to_date('19'||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')
/
share|improve this answer
1  
@kubanczyk: I wa inspired by the examples at docs.oracle.com/cd/E11882_01/server.112/e41084/… . But I also prefere a interval literal to a function of a string interval. so I will change it –  miracle173 Aug 24 '13 at 21:47
    
Thank you for pointing out that. I didn't noticed that. I have upvoted your answer. Helped me to learn alot than just solving my issue. –  Maximin Aug 27 '13 at 10:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.