这两个函数相互转化时有时候经常控制不好时间的格式,写个简单的例子被查。[@more@]
C:>sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 10 09:08:11 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2608762
SQL> select scn_to_timestamp(2608762) from dual;
SCN_TO_TIMESTAMP(2608762)
---------------------------------------------------------------------------
10-3月 -09 09.11.40.000000000 上午
SQL> select to_timestamp('10-3月 -09 09.11.40.000000000 上午') from dual;
TO_TIMESTAMP('10-3月-0909.11.40.000000000上午')
---------------------------------------------------------------------------
10-3月 -09 09.11.40.000000000 上午
SQL> select timestamp_to_scn(to_timestamp('10-3月 -09 09.11.40.000000000 上午'))
from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('10-3月-0909.11.40.000000000上午'))
-----------------------------------------------------------------
2608761
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:>set nls_lang=american_
C:>sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 10 09:18:02 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2608901
SQL> select scn_to_timestamp(2608762) from dual;
SCN_TO_TIMESTAMP(2608762)
---------------------------------------------------------------------------
10-MAR-09 09.11.40.000000000 AM
SQL> select timestamp_to_scn(to_timestamp('10-3月 -09 09.11.40.000000000 上午'))
from dual;
select timestamp_to_scn(to_timestamp('10-3月 -09 09.11.40.000000000 上午')) from
dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select timestamp_to_scn(to_timestamp('10-MAR-09 09.11.40.000000000 AM')) fr
om dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('10-MAR-0909.11.40.000000000AM'))
---------------------------------------------------------------
2608761
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:>set nls_lang=american_japan.
C:>sqlplus as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 10 09:19:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2609060
SQL> select scn_to_timestamp(2608762) from dual;
SCN_TO_TIMESTAMP(2608762)
---------------------------------------------------------------------------
09-03-10 09:11:40.000000000
SQL> select timestamp_to_scn(to_timestamp('09-03-10 09:11:40.000000000')) from d
ual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('09-03-1009:11:40.000000000'))
------------------------------------------------------------
2608761
SQL>