?
Solved

Updating a date field in PL/SQL

Posted on 2008-10-31
3
Medium Priority
?
900 Views
Last Modified: 2013-12-19
I would like to know the easiest way to update a date field in pl/sql.  I have a date field that was imported incorrectly from an .csv file into my oracle table.

The date field came in as follows:
08/24/0001 which should be 08/20/2001
09/01/0098 which should be 09/01/1998

Please help.
0
Comment
Question by:ewgf2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 10

Accepted Solution

by:
dbmullen earned 1400 total points
ID: 22854946
assuming there are no rows in the future (ie.. 2009, 2010, 2011...)

update tablea
set date_column = add_months(date_column,12 * 2000)
where to_char(date_column,'yyyy') between '0000' and '0008'
;

update tablea
set date_column = add_months(date_column,12 * 1900)
where to_char(date_column,'yyyy') between '0009' and '0099'
;

commit;
0
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 600 total points
ID: 22855017
update a
set d = case when d > to_date('01/01/0050') then add_months(d, 1900 * 12)
else add_months(d, 2000 * 12)
end
;
--d is the column name

0
 

Author Closing Comment

by:ewgf2002
ID: 31512222
I wish to thank you both.  dbmullen's solution narrowed the results to what i needed.  both solutions will work thought.  thanks again.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question