Convert Timestamp fields to Date fields

[dtmacd]

Hi,

I have a 9i system with 9,000,000 records in - we have discovered all dates have been built as timestamp type.

Really need to store Date type.

Can someone advise on how best to do this - I am thinking about using PLSQL, & trying to loop through all table.columns checking for timestamp type, if found create a temp field of type date, to_char(timstamp), update the records, drop timestamp column, rename temp date field to column name.

Is this the best course of action?

Many thanks,

Dawn

 

[johnherman]

That's the usual procedure for changing the data type of a column. Good luck.

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[dtmacd]

Many thanks for the confirmation - any chance of a few pointers to the code requireed - I am a newbie to PL/SQL.

 

[sem]

1. You don't need pl/sql, just plane sql: ALTER TABLE and UPDATE.
2. I do not understand how you plan to use TO_CHAR to convert TIMESTAMP to DATE.

Regards, Dima

http://seminihin.narod.ru

 

[dtmacd]

The reason I thought I needed pl/sql was twofold...

1. I want to loop through all tables and columns - I have many of these & want to just convert all TIMESTAMPs int DATEs.

2. I thought the pl/sql would be faster.

 

[sem]

A good old method is to use sql*plus to generate such commands to files and then execute them. Just as an example (you need some SET commands to make it work)

spool cmd.sql
select 'alter table '||table_name||' add ('||column_name||'_DT date);'
from user_tab_columns
where DATA_TYPE LIKE 'TIMESTAMP%';
spool off
@cmd
[code]

Similarly you may generate other statements.

Regards, Dima
[URL unfurl="true"]http://seminihin.narod.ru[/URL]