Re: help - changing all dates in database??

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 Thu May 02 2002 - 20:53:52 CEST