Re: help - changing all dates in database??

From: Edwin <vanmeerendonk_at_wxs.nl>
Date: 3 May 2002 04:20:35 -0700
Message-ID: <31354201.0205030320.6aa7799c_at_posting.google.com>

I would go for something like:
declare
  cursor c_utc
  is
    SELECT table_name, column_name
    FROM user_tab_columns
    WHERE data_type = 'DATE'
  ;
  l_cur integer := DBMS_SQL.OPEN_CURSOR;   l_res integer;
begin
  for r_utc in c_utc
  loop
    dbms_sql.parse

      ( l_cur
      , 'update '||r_utc.table_name


      ||' set '||r_utc.column_name


      ||' = to_date(''2000''||to_char('||r_utc.column_name


      ||',''MMDDHH24:MI:SS''),''YYYYMMDDHH24:MI:SS'')'


      ||' where to_number(to_char('||r_utc.column_name||',''YYYY'')) < 2000'


      , dbms_sql.native
      );

    l_res := dbms_sql.execute(l_cur);
  end loop;
  dbms_sql.close_cursor (l_cur);
end;

You didn't mention your Oracle version. In 8i+ you should use the execute immediate instead of dbms_sql.

"Me" <kark_1999_at_yahoo.com> wrote in message news:<4XfA8.80030$WV1.24108703_at_typhoon.ne.ipsvc.net>...

> Sorry to disappoint you, but I work for a large software company that needs
> their demonstration database brought up to date. When they show customers
> transactions with dates of 1995, it doesn't look good.
> Anyway, I apologize for the "smells". However, what I was really shooting
> for was a way to put this in a way I wouldn't have to spell out each
> individual table/column name (there are 998 columns that are of type DATE -
> not exactly "class project" size). Anyway, I found a workaround.
> Thanks, and thanks for the attitude. I'm a front-end developer that just
> needed a little assistance with Oracle for this stupid little internal
> request.
> 
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CD188DA.1EEF445C_at_exesolutions.com...
> > Me wrote:
> >
> > > I have a strange problem that I must resolve:
> > > In my company's huge database, I have to look at all of the date columns

 and

> > > do the following....
> > > - If the year is < 2000, I have to change it to 2000.
> > > - If it is >= 2000, I can leave it as is.
> > > Now, I have 2 questions (or request for suggestions):
> > > 1. Is there a way to search the database and return every column or

 database
> > > that contains a date datatype?
> > > 2. Is there an easy way to inspect the contents of the date (just the  year)

> > > and make these changes?
> > >
> > > Any suggestions would be great!
> > > THANKS.
> >
> > It is all easy ...
> >
> > First finding all of the data columns.
> >
> > SELECT table_name, column_name
> > FROM user_tab_columns            -- or all_tab_columns
> > WHERE data_type = 'DATE';
> >
> > To inspect the columns
> >
> > SELECT DISTINCT(TRUNC(date_column_name))
> > FROM table_name
> > ORDER BY TRUNC(date_column_name);
> >
> > But this really smells of being a class project as no one, for any reason

 I can

> > conceive, would make the request you indicate.
> >
> > Daniel Morgan
> >

Received on Fri May 03 2002 - 13:20:35 CEST