如何格式化数据类型为 timestamp with time zone 的显示格式-优快云博客

数据类为 TIMESTAMP(6) WITH TIME ZONE 的字段在显示日期时总是这样：

SQL> select last_start_date from user_scheduler_jobs;

LAST_START_DATE
-----------------------------------
21-MAR-06 03.00.00.421000 AM +08:00

20-MAR-06 10.00.02.734000 PM +08:00
20-MAR-06 10.00.02.734000 PM +08:00

我不习惯.421000 AM +08:00 这一段，对我来说，精确到秒即可，前段时间琢磨着，DATE类型的显示格式可以被这样设置：ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'。于是尝试着往注册表里加NLS_TIMESTAMP_FORMAT 来格式化该字段的显示格式，但屡次测试，都失败。NND，问了ORACLE，才知道是这个参数：

SQL> create table tt(id1 timestamp, id2 timestamp with time zone);

Table created.

SQL> insert into tt select sysdate, sysdate from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt;

ID1 ID2
------------------------------------- -------------------------------------
21-MAR-06 03.00.12.000000 PM 21-MAR-06 03.00.12.000000 PM +08:00

SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from tt;

ID1 ID2
------------------------------------- -------------------------------------
2006-03-21 15:00:12 21-MAR-06 03.00.12.000000 PM +08:00

SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from tt;

ID1 ID2
------------------------------------- -------------------------------------
2006-03-21 15:00:12 2006-03-21 15:00:12

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL>

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

转载于:http://blog.itpub.net/1112/viewspace-103715/