Home » SQL & PL/SQL » SQL & PL/SQL » Update date
Update date [message #307713] Wed, 19 March 2008 11:47 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have a date column in a table, in which somehow the application has inserted incorrect year. For example, instead of 2006, it contains 0006, 0007, 0008, etc... I need to run an update on that column to update just the year.

INSERT INTO test
("MY_DATE")
VALUES
(to_date('3/19/0007 01:13:23','MM/DD/RRRR HH24:MI:SS'))
/
INSERT INTO test
("MY_DATE")
VALUES
(to_date('3/19/0008 01:13:23','MM/DD/RRRR HH24:MI:SS'))
/

Any suggestions?

Thanks for your assistance
Re: Update date [message #307714 is a reply to message #307713] Wed, 19 March 2008 11:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
For this case, you could just add 24,000 months:

UPDATE test SET my_date = Add_Months(my_date,24000);





Re: Update date [message #307716 is a reply to message #307714] Wed, 19 March 2008 11:57 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Thank you. That worked!
Re: Update date [message #307722 is a reply to message #307713] Wed, 19 March 2008 12:15 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You probably want to find the problem because I can guarantee it is going to happen again.
Looks like a poor application design that expects 4 digit years and someone used a 2 digit year.
Previous Topic: ORA-06502: PL/SQL: numeric or value error: number precision too large
Next Topic: Need help with PLSQL query
Goto Forum:
  


Current Time: Mon Dec 05 18:59:17 CST 2016

Total time taken to generate the page: 5.46153 seconds