oracle scntotimestamp,scn_to_timestamp以及timestamp_to_scn的相互转化！

这两个函数相互转化时有时候经常控制不好时间的格式，写个简单的例子被查。[@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>