创建表测试表并插入数据

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博客

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

最新文章