Re: help - changing all dates in database??

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:43:38 CEST