Oracle 时间以及数字格式化
转自:http://blog.sina.com.cn/s/blog_03c54d1001010gi9.html
(1)日期及时间格式
经常遇到有朋友问询关于日期时间格式化的问题(或可以通过格式化轻易解决),经过参考Oracle SQL Reference官方文档,并附上详细的应用示例综合成文,希望能够对大家学习和使用有所帮助。本篇可视为"oracle著名及非著名函数介绍"的补充!
SQL> select *from v$version;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------------------------------
+08:00
SQL> show parameter nls;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string yyyy-mm-dd hh24:mi:ss
nls_language string SIMPLIFIED CHINESE格式串 说明及示例
---------- -----------------------
例如:
SQL> select to_char(sysdate,¨yyyy/mm/dd¨) from dual;
TO_CHAR(SYSDATE,¨YYYY/MM/DD¨)
-----------------------------
2007/12/14
SQL> select to_char(sysdate,¨yyyy-mm-dd¨) from dual;
TO_CHAR(SYSDATE,¨YYYY-MM-DD¨)
-----------------------------
2007-12-14例如:
SQL> select to_char(sysdate,¨AD YYYY-MM-DD¨) from dual;
TO_CHAR(SYSDATE,¨ADYYYY-MM-DD¨
------------------------------
公元 2007-12-14例如
SQL> select to_char(sysdate,¨BC YYYY-MM-DD¨) from dual;
TO_CHAR(SYSDATE,¨BCYYYY-MM-DD¨
------------------------------公元 2007-12-14
例如:
SQL> select to_char(sysdate,¨YYYY-MM-DD AM hh24:mi:ss¨) from dual;
TO_CHAR(SYSDATE,¨YYYY-MM-DDAMH
------------------------------
2007-12-14 下午 13:41:58例如:
SQL> select to_char(sysdate,¨YYYY-MM-DD PM hh24:mi:ss¨) from dual;
TO_CHAR(SYSDATE,¨YYYY-MM-DDPMH
------------------------------
2007-12-14 下午 13:42:23提示:
例如:
SQL> select to_char(to_date(¨2000-8-3¨,¨YYYY-MM-DD¨),¨cc¨) from dual;
TO_CHAR(TO_DATE(¨2000-8-3¨,¨YY
------------------------------
20
SQL> select to_char(to_date(¨2001-8-3¨,¨YYYY-MM-DD¨),¨cc¨) from dual;
TO_CHAR(TO_DATE(¨2001-8-3¨,¨YY
------------------------------
21例如:
SQL> select to_char(to_date(¨2007-12-14¨,¨yyyy-mm-dd¨),¨D¨) from dual;
TO_CHAR(TO_DATE(¨2007-12-14¨,¨
------------------------------
6例如:
SQL> select to_char(to_date(¨2007-12-14¨,¨yyyy-mm-dd¨),¨DD¨) from dual;
TO_CHAR(TO_DATE(¨2007-12-14¨,¨
------------------------------
14例如:
SQL> select to_char(to_date(¨2007-12-14¨,¨yyyy-mm-dd¨),¨DDD¨) from dual;
TO_CHAR(TO_DATE(¨2007-12-14¨,¨
------------------------------
348例如:
SQL> select to_char(to_date(¨2007-12-14¨,¨yyyy-mm-dd¨),¨Day¨) from dual;
TO_CHAR(TO_DATE(¨2007-12-14¨,¨
------------------------------
星期五例如:
SQL> select to_char(sysdate,¨MON¨) from dual;
TO_CHAR(SYSDATE,¨MON¨)
----------------------
12 月与DY同,中文看不出简写效果。
例如:
SQL> select to_char(sysdate,¨MONTH¨) from dual;
TO_CHAR(SYSDATE,¨MONTH¨)
------------------------
12 月例如:
SQL> select to_char(sysdate,¨year¨) from dual;
TO_CHAR(SYSDATE,¨YEAR¨)
------------------------------------------
two thousand seven例如:
SQL> select to_char(sysdate,¨yyyy¨) from dual;
TO_CHAR(SYSDATE,¨YYYY¨)
-----------------------
2007例如:
SQL> select to_char(sysdate,¨MM¨) from dual;
TO_CHAR(SYSDATE,¨MM¨)
---------------------
12例如:
SQL> select to_char(sysdate,¨am hh¨) from dual;
TO_CHAR(SYSDATE,¨AMHH¨)
-----------------------
下午 02例如:
SQL> select to_char(sysdate,¨am hh12¨) from dual;
TO_CHAR(SYSDATE,¨AMHH12¨)
-------------------------
下午 02例如:
SQL> select to_char(sysdate,¨am hh24¨) from dual;
TO_CHAR(SYSDATE,¨AMHH24¨)
-------------------------
下午 14例如:
SQL> select to_char(sysdate,¨MI¨) from dual;
TO_CHAR(SYSDATE,¨MI¨)
---------------------
05例如:
SQL> select to_char(sysdate,¨ss¨) from dual;
TO_CHAR(SYSDATE,¨SS¨)
---------------------
34例如:
SQL> select to_char(systimestamp,¨ff4¨) from dual;
TO_CHAR(S
---------
6710例如:
SQL> select to_char(sysdate,¨y,yyy¨) from dual;
TO_CHAR(SYSDATE,¨Y,YYY¨)
------------------------
2,007例如:
SQL> select to_char(to_date(¨187-4-3¨,¨yyyy-mm-dd¨),¨IYYY¨) from dual;
TO_CHAR(TO_DATE(¨187-4-3¨,¨YYY
------------------------------
0187例如:
SQL> select to_char(sysdate,¨yy¨) from dual;
TO_CHAR(SYSDATE,¨YY¨)
---------------------
07例如:
SQL> select to_char(to_date(¨2007-6-18¨,¨yyyy-mm-dd¨),¨w¨) from dual;
TO_CHAR(TO_DATE(¨2007-6-18¨,¨Y
------------------------------
3例如:
SQL> select to_char(to_date(¨2007-6-18¨,¨yyyy-mm-dd¨),¨ww¨) from dual;
TO_CHAR(TO_DATE(¨2007-6-18¨,¨Y
------------------------------
25例如:
SQL> select to_char(sysdate,¨IW¨) from dual;
TO_CHAR(SYSDATE,¨IW¨)
---------------------
50例如:
SQL> select to_char(to_date(¨2007-11-3¨,¨yyyy-mm-dd¨),¨RM¨) from dual;
TO_CHAR(TO_DATE(¨2007-11-3¨,¨Y
------------------------------
XI例如:
SQL> select to_char(to_date(¨2007-2-3¨,¨yyyy-mm-dd¨),¨q¨) from dual;
TO_CHAR(TO_DATE(¨2007-2-3¨,¨YY
------------------------------
1例如:
SQL> select to_char(sysdate,¨J¨) from dual;
TO_CHAR(SYSDATE,¨J¨)
--------------------
2454449例如:
SQL> select to_char(sysdate,¨sssss¨) from dual;
TO_CHAR(SYSDATE,¨SSSSS¨)
------------------------
55141例如:
SQL> select to_char(sysdate,¨DL¨) from dual;
TO_CHAR(SYSDATE,¨DL¨)
------------------------
2007 年12月14日 星期五例如:
SQL> select to_char(sysdate,¨Ds¨) from dual;
TO_CHAR(SYSDATE,¨DS¨)
---------------------
2007-12-14例如:
SQL> select to_char(sysdate,¨ts¨) from dual;
TO_CHAR(SYSDATE,¨TS¨)
---------------------
下午 3:21:20例如:
SQL> alter session set nls_language=¨SIMPLIFIED CHINESE¨;
会话已更改。
SQL> select to_char(sysdate,¨dy¨) from dual;
TO_CHAR(SYSD
------------
星期五
嗯?怎么没变?黑黑中文没法简了嘛,这样试试~~
SQL> alter session set nls_language=¨AMERICAN¨;
Session altered.
SQL> select to_char(sysdate,¨dy¨) from dual;
TO_CHA
------
fri如果输入的两位数是00-49:
如果当前年份属于00-49,则返回成本世纪所在年份;
如果当前年份属于50-99,则返回成下世纪所在年份。
如果输入的两位数是50-99:
如果当前年份属于00-49,则返回成上世纪所在年份
如果当前年份属于50-99,则返回成本世界所在年份
注:这种自动转换只适用于 String -> Date 的转换,如果是 Date -> String,那就按照 Date 存储的值来转换,此时再用 RR/RRRR 已经没有意义了,因为 Oracle 内部存储的年份是四位数的。例如:
SQL> select sysdate from dual;
SYSDATE
-------------------
2007-12-18 15:11:39
SQL> select to_date(¨12¨,¨rr¨) from dual;
TO_DATE(¨12¨,¨RR¨)
-------------------
2012-12-01 00:00:00
SQL> select to_date(¨51¨,¨rr¨) from dual;
TO_DATE(¨51¨,¨RR¨)
-------------------
1951-12-01 00:00:00RRRR 输入值可以是2位也可以是4位,2位时规则与RR完全相同。
例如:
SQL> select to_date(¨97-11-3¨,¨rrrr-mm-dd¨) from dual;
TO_DATE(¨97-11-3¨,¨RRRR-MM-DD¨
------------------------------
1997-11-3
SQL> select to_date(¨17-11-3¨,¨rrrr-mm-dd¨) from dual;
TO_DATE(¨17-11-3¨,¨RRRR-MM-DD¨
------------------------------
2017-11-3提示:FM及FX用于to_*函数中控制空白填充或精确检查,其提供的是一个格式 化方案,并且具备相当的持久能力,比如当你第一次调用之后即开始生效,后续再调用时即使不指定FM/FX,也仍然会生效。当你再次调用时失效,第三次调用 时又开始生效,以此类推(有点儿绕,不明白的看下面的示例,示例还看不明白的直接问我,问我也问不明白地话..........黑,估计你也用不着,算 鸟)。。。。。FM 填充模式。
Oracle 默认使用空白字符填充被格式化的元素,确保宽度一致。例如:
例如:
SQL> select to_char(to_date(¨2007-3-1¨,¨yyyy-mm-dd¨),¨month¨) mm,length(to_char(to_date(¨2007-3-1¨,¨yyyy-mm-dd¨),¨month¨)) ll from dual;
MM LL
--------- ----------
march 9
SQL> select to_char(to_date(¨2007-12-1¨,¨yyyy-mm-dd¨),¨month¨) mm,length(to_char(to_date(¨2007-12-1¨,¨yyyy-mm-dd¨),¨month¨)) ll from dual;
MM LL
--------- ----------
december 9如上例可以看到,不管几月份,其返回所占长度是一致的。
例如:
SQL> select to_char(to_date(¨2007-3-1¨,¨yyyy-mm-dd¨),¨fmmonth¨) mm,length(to_char(to_date(¨2007-3-1¨,¨yyyy-mm-dd¨),¨fmmonth¨)) ll from dual;
MM LL
----- ----------
march 5
SQL> select to_char(to_date(¨2007-12-1¨,¨yyyy-mm-dd¨),¨fmmonth¨) mm,length(to_char(to_date(¨2007-12-1¨,¨yyyy-mm-dd¨),¨fmmonth¨)) ll from dual;
MM LL
-------- ----------
december 8
与上对比,看出区别来了吧。例如:
SQL> alter session set nls_date_format=¨fmyyyy-mm-dd hh24:mi:ss¨;
Session altered.
SQL> select to_date(¨2007-8-1 03:03:04¨,¨yyyy-mm-dd hh24:mi:ss¨) from dual;
TO_DATE(¨2007-8-103
-------------------
2007-8-1 3:3:4
SQL> alter session set nls_date_format=¨yyyy-mm-dd hh24:mi:ss¨;
Session altered.
SQL> select to_date(¨2007-8-1 03:03:04¨,¨yyyy-mm-dd hh24:mi:ss¨) from dual;
TO_DATE(¨2007-8-103
-------------------
2007-08-01 03:03:04
SQL> alter session set nls_date_format=¨fmyyyy-mm-fmdd hh24:fmmi:ss¨;
Session altered.
SQL> select to_date(¨2007-8-1 03:03:04¨,¨yyyy-mm-dd hh24:mi:ss¨) from dual;
TO_DATE(¨2007-8-103
-------------------
2007-8-01 03:3:4例如:
SQL> select to_date(¨2007-9-26¨,¨yyyy-mm-dd¨) from dual;
TO_DATE(¨2007-9-26¨
-------------------
2007-09-26 00:00:00
SQL> select to_date(¨2007-9-26¨,¨fxyyyy-mm-dd¨) from dual;
select to_date(¨2007-9-26¨,¨fxyyyy-mm-dd¨) from dual
*
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item
SQL> select to_date(¨2007-09-26¨,¨fxyyyy-mm-dd¨) from dual;
TO_DATE(¨2007-09-26
-------------------
2007-09-26 00:00:00
SQL> select to_date(¨2007-9-26¨,¨fxyyyy-fmmm-dd¨) from dual;
TO_DATE(¨2007-9-26¨
-------------------
2007-09-26 00:00:00
SQL> select to_date(¨2007-9-26¨,¨fxyyyy/fmmm/dd¨) from dual;
select to_date(¨2007-9-26¨,¨fxyyyy/fmmm/dd¨) from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string例如:
SQL> select to_char(systimestamp,¨hh24:mi:ssxff¨) from dual;
TO_CHAR(SYSTIMESTAMP,¨HH24:MI:
------------------------------
16:21:12.609000例如:
SQL> select to_char(systimestamp,¨tzr:tzd¨) from dual;
TO_CHAR(SYSTIMESTAMP,¨TZR:TZD¨)
---------------------------------------
+08:00:例如:
SQL> select to_char(systimestamp,¨tzh:tzm¨) from dual;
TO_CHA
------
+08:00SQL> select to_date(¨2007-12-6¨, ¨yyyy-mm-dd¨)
2 - to_char(to_date(¨2007-12-6¨, ¨yyyy-mm-dd¨), ¨d¨) + 2
3 from dual;
TO_DATE(¨2
----------
2007-12-03
SQL> select trunc(to_date(¨2007-12-6¨, ¨yyyy-mm-dd¨), ¨d¨) + 1 rn from dual;
TRUNC(TO_DATE(¨2007-12-6¨,¨YYY
------------------------------
2007-12-3
SQL> select next_day(to_date(¨2007-12-6¨, ¨yyyy-mm-dd¨) - 7, ¨ 星期一¨) from dual;
NEXT_DAY(TO_DATE(¨2007-12-6¨,¨
------------------------------
2007-12-3SQL> select max(rn)
2 from (select to_date(¨2007-12-¨ || rownum, ¨yyyy-mm-dd¨) rn
3 from dual
4 connect by rownum <=
5 (to_char(to_date(¨2007-12-31¨, ¨yyyy-mm-dd¨), ¨dd¨))) a
6 where to_char(a.rn, ¨d¨) = 1
7 ;
MAX(RN)
-----------
2007-12-30
SQL> select next_day(trunc(to_date(¨2007-12-31¨, ¨yyyy-mm-dd¨), ¨month¨)+31-7,¨ 星期日¨) from dual;
NEXT_DAY(TRUNC(TO_DATE(¨2007-1
------------------------------
2007-12-30SQL> select to_date(¨127¨,¨ddd¨) from dual;
TO_DATE(¨1
----------
2007-05-07格式串 说明及示例
---------- -----------------------
例如:
SQL> select to_char(389999.00,¨99,999,999¨) from dual;
TO_CHAR(389
-----------
389,999例如:
SQL> select to_char(18,¨999.000¨) from dual;
TO_CHAR(
--------
18.000例如:
SQL> select to_char(18,¨$999¨) from dual;
TO_CH
-----
$18
SQL> select to_char(18,¨999$¨) from dual;
TO_CH
-----
$18例如:
SQL> select to_char(18.0,¨0999¨) from dual;
TO_CH
-----
0018例如:
SQL> select to_char(18.0,¨9999¨) from dual;
TO_CH
-----
18例如:
SQL> select to_char(0.18,¨B0999¨) from dual;
TO_CH
-----例如:
SQL> select to_char(128.18,¨C999999.99¨) from dual;
TO_CHAR(128.18,¨C
-----------------
CNY128.18例如:
SQL> select to_char(128.18,¨9999D99¨) from dual;
TO_CHAR(
--------
128.18
SQL> alter session set nls_numeric_characters=¨:"¨;
会话已更改。
SQL> select to_char(128.18,¨9999D99¨) from dual;
TO_CHAR(
--------
128:18例如:
SQL> select to_char(128.18,¨9EEEE¨) from dual;
TO_CHAR
-------
1E+02例如:
SQL> select to_char(11128.18,¨99G999¨) from dual;
TO_CHAR
-------
11,128
SQL> alter session set nls_numeric_characters=¨:"¨;
会话已更改。
SQL> select to_char(11128.18,¨99G999¨) from dual;
TO_CHAR
-------
11"128例如:
SQL> select to_char(999,¨L999¨) from dual;
TO_CHAR(999,¨L
--------------
¥999
SQL> alter session set nls_currency=¨haha¨;
会话已更改。
SQL> select to_char(999,¨L999¨) from dual;
TO_CHAR(999,¨L
--------------
haha999例如:
SQL> select to_char(-18,¨999MI¨) from dual;
TO_C
----
18-
SQL> select to_char(18,¨999MI¨) from dual;
TO_C
----
18例如:
SQL> select to_char(-18,¨999PR¨) from dual;
TO_CH
-----
<18>
SQL> select to_char(18,¨999PR¨) from dual;
TO_CH
-----
18例如:
SQL> select to_char(18,¨RN¨) from dual;
TO_CHAR(18,¨RN¨
---------------
XVIII
SQL> select to_char(18,¨rn¨) from dual;
TO_CHAR(18,¨RN¨
---------------
xviii例如:
SQL> select to_char(18,¨S9999¨) from dual;
TO_CH
-----
+18
SQL> select to_char(18,¨9999S¨) from dual;
TO_CH
-----
18+例如:
SQL> select TO_CHAR(128,¨tme¨) from dual;
TO_CHAR(128,¨TME¨)
----------------------------
1.28E+02
SQL> select TO_CHAR(1.28E+02,¨tm¨) from dual;
TO_CHAR(1.28E+02,¨TM¨)
----------------------------
128例如:
SQL> select TO_CHAR(128,¨999U¨) from dual;
TO_CHAR(128,¨9
--------------
128 ¥
SQL> alter session set nls_dual_currency=¨aaa¨;
会话已更改。
SQL> select TO_CHAR(128,¨999U¨) from dual;
TO_CHAR(128,¨9
--------------
128aaa例如:
SQL> select TO_CHAR(128,¨999V999¨) from dual;
TO_CHAR
-------
128000例如:
SQL> select TO_CHAR(16,¨XXXX¨) from dual;
TO_CH
-----
10