环境:
[oracle@localhost ~]$ sqlplus -v
SQL*Plus: Release 10.2.0.1.0 - Production
sys@ORCL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
sys@ORCL> alter session set nls_language='simplified chinese';
Session altered.
sys@ORCL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
㈠ 格式串:/ - : # 等等
指定返回字符串的分隔符
sys@ORCL> select to_char(sysdate,'yyyy/mm/dd') "/" from dual;
/
----------
2013/01/03
sys@ORCL> select to_char(sysdate,'yyyy-mm-dd') "-" from dual;
-
----------
2013-01-03
sys@ORCL> select to_char(sysdate,'yyyy#mm#dd') "#" from dual;
#
----------
2013#01#03
㈡ 常见标识
公元标识:AD 或 BC
sys@ORCL> select to_char(sysdate,'AD yyyy-mm-dd') "AD" from dual;
AD
--------------------
公元 2013-01-03
子午线标识:AM PM
sys@ORCL> select to_char(sysdate,'yyyy-mm-dd AM hh24:mi:ss') "AM" from dual;
AM
--------------------------
2013-01-03 下午 19:24:36
世纪标识:CC SCC
S前缀指定如遇公元前的显示,会在显示前加(-)
如果年份中最后两位数字在01到99(含)之间,则返回值等于年份前两位+1
如果年份中最后两位数字是00,则返回值与年份前两位相同
sys@ORCL> select to_char(to_date('2013-1-3','YYYY-MM-DD'),'CC') "CC" from dual;
CC
--
21
sys@ORCL> select to_char(to_date('2000-1-3','YYYY-MM-DD'),'CC') "CC" from dual;
CC
--
20