Alter table Modify Datatype [message #316895] |
Mon, 28 April 2008 16:49  |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Hi,
I have a requirement to modify the datatype of 1 column from 11 tables, by mistake it was created with timestamp, we want it to be date.
I did with this approach, out of 11 tables 2 are big with 70 mil rows.
update routing set last_updt_ts=to_date(last_updt_ts_old);
alter table routing rename last_updt_ts to last_updt_ts_old;
alter table routing add last_updt_Ts date;
update routing set last_updt_ts=to_date(last_updt_ts_old);
ORA-01830: date format picture ends before converting entire input string
then i use this
update routing set last_updt_ts=to_char(last_updt_ts_old,'dd-mon-yyyy ')
This updates but set the time portion of the date for all rows to 30/08/2006 12:00:00
which is wrong, data has different times,
What should be my approach to modify these datatypes, fastest and accurate.
Oracle 10g Rel 2.
Thanks
|
|
|
|
Re: Alter table Modify Datatype [message #316905 is a reply to message #316904] |
Mon, 28 April 2008 19:04   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I tested this in 11g, so it might not work in 10g and you may have to settle for wrapping a to_date around a to_char with complete matching format models including the time portion specified.
SCOTT@orcl_11g> DESC routing
Name Null? Type
----------------------------------------- -------- ----------------------------
LAST_UPDT_TS TIMESTAMP(6)
SCOTT@orcl_11g> SELECT * FROM routing
2 /
LAST_UPDT_TS
---------------------------------------------------------------------------
28-APR-08 05.02.37.546000 PM
SCOTT@orcl_11g> ALTER TABLE routing MODIFY (last_updt_ts DATE)
2 /
Table altered.
SCOTT@orcl_11g> DESC routing
Name Null? Type
----------------------------------------- -------- ----------------------------
LAST_UPDT_TS DATE
SCOTT@orcl_11g> SELECT * FROM routing
2 /
LAST_UPDT_TS
--------------------
28-apr-2008 17:02:38
SCOTT@orcl_11g>
[Updated on: Mon, 28 April 2008 19:06] Report message to a moderator
|
|
|
Re: Alter table Modify Datatype [message #317162 is a reply to message #316905] |
Tue, 29 April 2008 14:07   |
azeem87
Messages: 116 Registered: September 2005 Location: dallas
|
Senior Member |
|
|
Thanks Barb and anacedent,
Barb i don't have 11g to Test , But in 10g it didn't works with your approach.
I have tried this is in 10g but the problem is , since i can't apply to_char, i used TRUNC to update and that truncated the time portion, and another draw back is if there are any constraint / Indexes i have to recreate them later,
alter table rtn_acct_copy rename column last_updt_ts to last_updt_ts_old;
alter table rtn_acct_copy add last_updt_ts date;
update rtn_acct_copy set last_updt_ts=trunc(last_updt_ts_old);
alter table rtn_acct_copy drop column last_updt_ts_old;
alter table rtn_acct_copy rename column last_updt_ts_old to last_updt_ts;
And if i run to truncate the time portion, but still i can't modify the data type.
Update rtn_acct_copy set last_updt_ts=trunc(last_updt_ts) And after this if i try to modify the column with Date datatype it's giving this error
ALTER TABLE rtn_acct_copy MODIFY (last_updt_ts_old date);
ORA-01439: column to be modified must be empty to change datatype
Anacedent you were talking about to_char(to_date), How should i use it in my case,
Thanks
|
|
|
Re: Alter table Modify Datatype [message #317175 is a reply to message #317162] |
Tue, 29 April 2008 16:19  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can use TO_CHAR to convert your TIMESTAMP to VARCHAR2 using a format that includes the time portion, then wrap TO_DATE around that to convert it to DATE, using the same date format that includes the time portion.
SCOTT@orcl_11g> DESC routing
Name Null? Type
----------------------------------------- -------- ----------------------------
LAST_UPDT_TS NOT NULL TIMESTAMP(6)
SCOTT@orcl_11g> SELECT * FROM routing
2 /
LAST_UPDT_TS
---------------------------------------------------------------------------
29-APR-08 02.17.05.572000 PM
SCOTT@orcl_11g> ALTER TABLE routing RENAME COLUMN last_updt_ts TO last_updt_ts_old
2 /
Table altered.
SCOTT@orcl_11g> ALTER TABLE routing ADD last_updt_ts DATE
2 /
Table altered.
SCOTT@orcl_11g> UPDATE routing
2 SET last_updt_ts =
3 TO_DATE
4 (TO_CHAR
5 (last_updt_ts_old,
6 'dd-mon-yyyy hh24:mi:ss'),
7 'dd-mon-yyyy hh24:mi:ss')
8 /
1 row updated.
SCOTT@orcl_11g> ALTER TABLE routing DROP COLUMN last_updt_ts_old
2 /
Table altered.
SCOTT@orcl_11g> DESC routing
Name Null? Type
----------------------------------------- -------- ----------------------------
LAST_UPDT_TS DATE
SCOTT@orcl_11g> SELECT * FROM routing
2 /
LAST_UPDT_TS
--------------------
29-apr-2008 14:17:05
SCOTT@orcl_11g>
|
|
|