Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 929
  • Last Modified:

Updating a date field in PL/SQL

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
ewgf2002
Asked:
ewgf2002
2 Solutions
 
dbmullenCommented:
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
 
jamesguCommented:
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
 
ewgf2002Author Commented:
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now