Home » SQL & PL/SQL » SQL & PL/SQL » Alter table Modify Datatype (Oracle 10g Rel 2)
Alter table Modify Datatype [message #316895] Mon, 28 April 2008 16:49 Go to next message
azeem87
Messages: 95
Registered: September 2005
Location: dallas
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 #316904 is a reply to message #316895] Mon, 28 April 2008 18:59 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

I suggest you read up on TO_CHAR & TO_DATE functions
Re: Alter table Modify Datatype [message #316905 is a reply to message #316904] Mon, 28 April 2008 19:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
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 Go to previous messageGo to next message
azeem87
Messages: 95
Registered: September 2005
Location: dallas
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 Go to previous message
Barbara Boehmer
Messages: 8623
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> 


Previous Topic: Passing Dynamic Collection to SubRoutines
Next Topic: How to "synchronize" procedures in PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 04:14:16 CST 2016

Total time taken to generate the page: 0.22402 seconds