oracle将表中date数据类型修改为timestamp数据类型

   

创建表测试表并插入数据

SQL> create table test(a number(10),b date);

Table created.

SQL> insert into test values(1,sysdate);

1 row created.

SQL> insert into test values(2,sysdate);

1 row created.

SQL> insert into test values(3,sysdate);

1 row created.

SQL> insert into test values(4,sysdate);

1 row created.

SQL> commit;

SQL> select * from test;

         A B
---------- ---------
         1 10-SEP-12
         2 10-SEP-12
         3 10-SEP-12
         4 10-SEP-12

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(10)
 B                                                  DATE

 

 

修改数据类型

 

SQL> alter table test modify b timestamp;

Table altered.

SQL> select * from test;

         A
----------
B
---------------------------------------------------------------------------
         1
10-SEP-12 11.46.27.000000 AM

         2
10-SEP-12 11.46.41.000000 AM

         3
10-SEP-12 11.47.39.000000 AM

         A
----------
B
---------------------------------------------------------------------------
         4
10-SEP-12 11.47.53.000000 AM

 

 

 

 

 

 

 

SQL> alter table test modify b date;

Table altered.

SQL> select to_char(b,'yyyy-mm-dd hh24:mi:ss') from test
  2  ;

TO_CHAR(B,'YYYY-MM-
-------------------
2012-09-10 11:46:27
2012-09-10 11:46:41
2012-09-10 11:47:39
2012-09-10 11:47:53

SQL> alter table test modify b timestamp;

Table altered.

SQL> select to_char(b,'yyyy-mm-dd hh24:mi:ss') from test;

TO_CHAR(B,'YYYY-MM-
-------------------
2012-09-10 11:46:27
2012-09-10 11:46:41
2012-09-10 11:47:39
2012-09-10 11:47:53

SQL> select * from test;

         A
----------
B
---------------------------------------------------------------------------
         1
10-SEP-12 11.46.27.000000 AM

         2
10-SEP-12 11.46.41.000000 AM

         3
10-SEP-12 11.47.39.000000 AM

         A
----------
B
---------------------------------------------------------------------------
         4
10-SEP-12 11.47.53.000000 AM

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27146953/viewspace-743022/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27146953/viewspace-743022/