Home » SQL & PL/SQL » SQL & PL/SQL » update the year in the date (oracle 9i database)
update the year in the date [message #289923] Wed, 26 December 2007 11:03 Go to next message
wannabee
Messages: 2
Registered: December 2007
Junior Member
There are a few dates in the database date column which are added incorrectly... The year is 0003 instead of 2003... I want to keep the dates but change the year on those dates...

example: 22-Mar-0003 should be 22-mar-2003 ...
How do I write the update statement?
Re: update the year in the date [message #289925 is a reply to message #289923] Wed, 26 December 2007 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t;
VAL
----------
10/12/0003
10/12/2005

2 rows selected.

SQL> update t set val=add_months(val,12*2000) where extract(year from val) < 100;

1 row updated.

SQL> select * from t;
VAL
----------
10/12/2003
10/12/2005

2 rows selected.

Regards
Michel
Re: update the year in the date [message #289927 is a reply to message #289925] Wed, 26 December 2007 11:25 Go to previous messageGo to next message
wannabee
Messages: 2
Registered: December 2007
Junior Member
Thank you! That is awesome! How do you guys do it!
Re: update the year in the date [message #289930 is a reply to message #289927] Wed, 26 December 2007 11:58 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I think a better thing to do is find out the cause of these bad dates (well, it's because you are using 2 digits instead of 4 digits for dates of course, but WHERE is this happening?).

Forms?
front-end tool?
third-party product?
java/perl/etc?
Previous Topic: How to find LOB format
Next Topic: Natural order by
Goto Forum:
  


Current Time: Sat Nov 02 12:32:24 CDT 2024