Home » SQL & PL/SQL » SQL & PL/SQL » updating date fields is there a better way using regular expressions?
updating date fields is there a better way using regular expressions? [message #260994] Tue, 21 August 2007 08:13 Go to next message
lijok
Messages: 68
Registered: April 2005
Member
Some erroneous dates got entered into the database. For example, I've dates stored as

mm/dd/yyyy
-----------
'01/01/0007'
'02/22/2007'
'08/14/2006'
'09/23/0006'

I need to correct the dates that got in with year as 0007 and 0006.

One way I could fix this is by the following sql
SQL> select count(*)
    from   prrecord where RECPAYMENTDATE is not null
          and substr(to_char(RECPAYMENTDATE,'yyyy'),0,1) = '0'

  COUNT(*)
----------
      7796

SQL> update	prrecord set RECPAYMENTDATE = to_date(to_char(RECPAYMENTDATE, 'mm/dd/')||'2'||substr(to_char(RECPAYMENTDATE, 'yyyy'),2,4), 'mm/dd/yyyy')
where	RECPAYMENTDATE is not null
	and substr(to_char(RECPAYMENTDATE,'yyyy'),0,1) = '0'

  COUNT(*)
----------
      7796


I've don't have much exposure to oracle regular expressions and did some search but couldn't find clear examples

Is there a better way to do this using regular expression?
Re: updating date fields is there a better way using regular expressions? [message #260995 is a reply to message #260994] Tue, 21 August 2007 08:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
update prrecord 
set    recpaymentdate = add_months(recpaymentdate, 2000 * 12) 
where  recpaymentdate < to_date('01-01-1000', 'dd-mm-yyyy')

No need for regular expressions, just simple date-arithmetics

[Updated on: Tue, 21 August 2007 08:27]

Report message to a moderator

Re: updating date fields is there a better way using regular expressions? [message #261002 is a reply to message #260995] Tue, 21 August 2007 08:55 Go to previous message
lijok
Messages: 68
Registered: April 2005
Member
Frank,
Thanks a lot. That was a good thought through and easy solution. It works perfectly fine.
Previous Topic: %Rowtype
Next Topic: create Sequences Base 16
Goto Forum:
  


Current Time: Fri Dec 09 13:37:52 CST 2016

Total time taken to generate the page: 0.15156 seconds