Re: help - changing all dates in database??
Date: Fri, 03 May 2002 11:38:07 GMT
Message-ID: <zEuA8.84132$WV1.24702023_at_typhoon.ne.ipsvc.net>
Thanks, Edwin!!
"Edwin" <vanmeerendonk_at_wxs.nl> wrote in message
news: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:38:07 CEST