Good Morning all,
Your source table must have had its columns defined as VARCHAR2 if the new table has the same.
When running this sort of query it's always best to describe/F4 the source table first. Just to be sure you will be getting what you want.
Look for numbers stored as strings, or worse, dates/times stored as strings (or numbers!) -- if you find some that are obviously wrong (by obviously, I mean using the wrong data type for the stored data values) then they need to be fixed on the new table.
I tend, mostly, to:
Create table new_t as select
<<list of good columns>>
Alter table new_t
Insert into new_t
<<list of good columns>>,
To_date(old_broken_date_col, 'format string')
-- run a few checks here to be sure we converted the broken columns ok.
Then add indexes, constraints etc as required.
If you get errors converting from string to the correct data type, it means that your data in the old table is "corrupt" -- most likely down to storing the data in completely thevwrong data type. (One of many bug bears of mine!)
The format string hinted at above tells the to_date function what format the string date column data should be. Hopefully yyyy/mm/dd but mileage varies, esp in the USA where mm/dd/yyyy is deemed correct.
Norm. [TeamT][Team new puppy ]