copying a table

[Dave Cullen]

I need to change the data type from TEXT to DATE in one field of an
Oracle 9i table. I cannot do that while there is data in the column. The
existing data is formatted text in the form mm/dd/yyyy.

I need to copy the existing table, clear the date column, change the
data type, and copy the data back in.

What's the best way to proceed?

Thanks

 

[John Vinson]

I need to change the data type from TEXT to DATE in one field of an
Oracle 9i table. I cannot do that while there is data in the column. The
existing data is formatted text in the form mm/dd/yyyy.

I need to copy the existing table, clear the date column, change the
data type, and copy the data back in.

What's the best way to proceed?

Thanks

I'd suggest instead *adding* a Date field; running an Update query to
copy the text dates into the date field; checking the data; and then
deleting the obsolete text field.

If the data is stored in Oracle you can (I'm guessing, haven't done
this) use PassThrough queries to run the ALTER TABLE ddl queries.

John W. Vinson[MVP]

 

[Dave Cullen]

Great idea. Thanks a bunch.

Dave

I'd suggest instead *adding* a Date field; running an Update query to
copy the text dates into the date field; checking the data; and then
deleting the obsolete text field.

If the data is stored in Oracle you can (I'm guessing, haven't done
this) use PassThrough queries to run the ALTER TABLE ddl queries.

John W. Vinson[MVP]