oracle將表中date資料型別修改為timestamp資料型別

xcdream6發表於2012-09-10
Oracle資料型別
   
建立表測試表並插入資料

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章