查看: 26203|回复: 68

[三思笔记]-日期时间及数字的格式化参数大全~

[复制链接]
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
发表于 2007-12-19 12:52 | 显示全部楼层 |阅读模式
环境:
[php]
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

--
[/php]
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 2007-12-19 12:53 | 显示全部楼层
一、日期及时间

[php]
格式串                说明及示例
----------                -----------------------------------------------------------
-/,.;:                        指定返回字串分隔符
例如:
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

AD/A.D.                公元标识
例如:
SQL> select to_char(sysdate,'AD YYYY-MM-DD') from dual;

TO_CHAR(SYSDATE,'ADYYYY-MM-DD'
------------------------------
公元 2007-12-14

BC/B.C.                公元标识
例如
SQL> select to_char(sysdate,'BC YYYY-MM-DD') from dual;

TO_CHAR(SYSDATE,'BCYYYY-MM-DD'
------------------------------
公元 2007-12-14

AM/A.M.        子午线标识
例如:
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

PM/P.M.                子午线标识
例如:
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

CC/SCC                世纪标识,S前缀指定如遇公元前的显示,会在显示前加(-)
提示:
如果年份中最后两位数字在01到99(含)之间,则返回值等于年份前两位+1
如果年份中最后两位数字是00,则返回值与年份前两位相同
例如:
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

--
[/php]

[ 本帖最后由 junsansi 于 2007-12-19 17:27 编辑 ]

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 2007-12-19 12:55 | 显示全部楼层
[php]
D                        指定日期在周中的数值(范围:1-7)
例如:
SQL> select to_char(to_date('2007-12-14','yyyy-mm-dd'),'D') from dual;

TO_CHAR(TO_DATE('2007-12-14',
------------------------------
6

DD                        指定日期在当月中的天数(范围:1-31)
例如:
SQL> select to_char(to_date('2007-12-14','yyyy-mm-dd'),'DD') from dual;

TO_CHAR(TO_DATE('2007-12-14',
------------------------------
14

DDD                指定日期在当年中的天数(范围:1-366)
例如:
SQL> select to_char(to_date('2007-12-14','yyyy-mm-dd'),'DDD') from dual;

TO_CHAR(TO_DATE('2007-12-14',
------------------------------
348

DAY                指定日期在周中的名称
例如:
SQL> select to_char(to_date('2007-12-14','yyyy-mm-dd'),'Day') from dual;

TO_CHAR(TO_DATE('2007-12-14',
------------------------------
星期五

MON                返回指定日期的月份简写
例如:
SQL> select to_char(sysdate,'MON') from dual;

TO_CHAR(SYSDATE,'MON')
----------------------
12月
与DY同,中文看不出简写效果。

MONTH                返回指定日期的月份全称
例如:
SQL> select to_char(sysdate,'MONTH') from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------
12月

YEAR/SYEAR        返回字符型年,此处s前缀与SCC处相同
例如:
SQL> select to_char(sysdate,'year') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
two thousand seven

YYYY/SYYYY        返回数字型年,此处s前缀与SCC处相同
例如:
SQL> select to_char(sysdate,'yyyy') from dual;

TO_CHAR(SYSDATE,'YYYY')
-----------------------
2007

MM                        返回指定日期的月份(范围:1-12)
例如:
SQL> select to_char(sysdate,'MM') from dual;

TO_CHAR(SYSDATE,'MM')
---------------------
12

HH                        小时(范围:1-12)
例如:
SQL> select to_char(sysdate,'am hh') from dual;

TO_CHAR(SYSDATE,'AMHH')
-----------------------
下午 02

HH12                小时(范围:1-12)
例如:
SQL> select to_char(sysdate,'am hh12') from dual;

TO_CHAR(SYSDATE,'AMHH12')
-------------------------
下午 02

HH24                小时(范围:0-23)
例如:
SQL> select to_char(sysdate,'am hh24') from dual;

TO_CHAR(SYSDATE,'AMHH24')
-------------------------
下午 14

MI                        返回指定时间的分钟(范围0-59)
例如:
SQL> select to_char(sysdate,'MI') from dual;

TO_CHAR(SYSDATE,'MI')
---------------------
05

SS                        返回指定时间的秒数(范围:0-59)
例如:
SQL> select to_char(sysdate,'ss') from dual;

TO_CHAR(SYSDATE,'SS')
---------------------
34

FF[1-9]                返回毫秒数,可指定长度1-9,默认6位
例如:
SQL> select to_char(systimestamp,'ff4') from dual;

TO_CHAR(S
---------
6710

Y,YYY                返回有逗号分隔显示的年
例如:
SQL> select to_char(sysdate,'y,yyy') from dual;

TO_CHAR(SYSDATE,'Y,YYY')
------------------------
2,007

I/IY/IYY/IYYY        返回ISO标准年
例如:
SQL> select to_char(to_date('187-4-3','yyyy-mm-dd'),'IYYY') from dual;

TO_CHAR(TO_DATE('187-4-3','YYY'
------------------------------
0187

Y/YY/YYY        以指定长度返回日期的年份
例如:
SQL> select to_char(sysdate,'yy') from dual;

TO_CHAR(SYSDATE,'YY')
---------------------
07

W                        返回指定日期在当月中的第X周(范围:1-5)
例如:
SQL> select to_char(to_date('2007-6-18','yyyy-mm-dd'),'w') from dual;

TO_CHAR(TO_DATE('2007-6-18',
------------------------------
3

WW                        返回指定日期在当年中的第X周(范围:1-53)
例如:
SQL> select to_char(to_date('2007-6-18','yyyy-mm-dd'),'ww') from dual;

TO_CHAR(TO_DATE('2007-6-18',
------------------------------
25

IW                        指定日期在当年中第X周(范围:1-52或1-53(润年))
例如:
SQL> select to_char(sysdate,'IW') from dual;

TO_CHAR(SYSDATE,'IW')
---------------------
50

RM                        以罗马数字形式返回月份(范围I-XII)
例如:
SQL> select to_char(to_date('2007-11-3','yyyy-mm-dd'),'RM') from dual;

TO_CHAR(TO_DATE('2007-11-3',
------------------------------
XI

Q                        返回指定日期的季度(范围:1-4)
例如:
SQL> select to_char(to_date('2007-2-3','yyyy-mm-dd'),'q') from dual;

TO_CHAR(TO_DATE('2007-2-3',
------------------------------
1

J                        自公元前4712年1月1日到指定日期的总天数
例如:
SQL> select to_char(sysdate,'J') from dual;

TO_CHAR(SYSDATE,'J')
--------------------
2454449

SSSSS                返回自午夜到指定时间共逝去的秒数(范围:0-86399)
例如:
SQL> select to_char(sysdate,'sssss') from dual;

TO_CHAR(SYSDATE,'SSSSS')
------------------------
55141


--
[/php]

[ 本帖最后由 junsansi 于 2007-12-19 17:28 编辑 ]

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 2007-12-19 12:56 | 显示全部楼层
[php]

DL                        根据数据库nls_date_format参数中的设置及nls_language,nls_territory参数中的格式返回长日期形式,有点儿绕,呵呵你试试就知道了
例如:
SQL> select to_char(sysdate,'DL') from dual;

TO_CHAR(SYSDATE,'DL')
------------------------
2007年12月14日 星期五

DS                        与上相同返回短日期形式
例如:
SQL> select to_char(sysdate,'Ds') from dual;

TO_CHAR(SYSDATE,'DS')
---------------------
2007-12-14

TS                        与DL,DS参数类似,不过ts返回的是时间
例如:
SQL> select to_char(sysdate,'ts') from dual;

TO_CHAR(SYSDATE,'TS')
---------------------
下午 3:21:20

DY                        返回日期简写
例如:
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

RR                        RR参数与YY参数很类似,但取值规则略有不同:
如果输入的两位数是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:00

RRRR                输入值可以是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

如上例可以看到,不管几月份,其返回所占长度是一致的。

如果是字符格式化,指定fm参数后将仅返回指定属性实际所占长度(不再以空格填充)。
例如:
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

与上对比,看出区别来了吧。

如果是数值格式化,fm参数会自动舍弃被格式化元素的前置0。
例如:
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


FX                        精确匹配。使用FX参数之后:
字符及对应格式必须严格一一对应,甚至连分隔符也需要相符。
不允许有多余空格。
数值参与格式需要完全对应(或通过fm参数自动补足0)
例如:
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

X                        本地的默认分隔符
例如:
SQL> select to_char(systimestamp,'hh24:mi:ssxff') from dual;

TO_CHAR(SYSTIMESTAMP,'HH24:MI:'
------------------------------
16:21:12.609000

以下四项为时区相关参数,明确表示某一个时区,应该联合使用 TZH TZM 或者 TZR TZD
TZD        /TZR               
例如:
SQL> select to_char(systimestamp,'tzr:tzd') from dual;

TO_CHAR(SYSTIMESTAMP,'TZR:TZD')
---------------------------------------
+08:00:

TZH/TZM
例如:
SQL> select to_char(systimestamp,'tzh:tzm') from dual;

TO_CHA
------
+08:00


--
[/php]

[ 本帖最后由 junsansi 于 2007-12-19 17:29 编辑 ]

使用道具 举报

回复
论坛徽章:
281
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-01-04 11:51:22蛋疼蛋
日期:2011-12-29 07:37:22迷宫蛋
日期:2011-12-26 14:19:41茶鸡蛋
日期:2011-11-17 09:20:52茶鸡蛋
日期:2011-11-10 22:42:38ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15茶鸡蛋
日期:2011-10-24 09:48:48ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47
 楼主| 发表于 2007-12-19 12:57 | 显示全部楼层
应用示例:
[php]

1、与指定日期在同一周,周1的日期是:
SQL> 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(
----------
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',
------------------------------
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-3

2、指定日期当月最后一个星期天:
SQL> 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-12-30

3、今年第127天是几号:
SQL> select to_date('127','ddd') from dual;

TO_DATE('1
----------
2007-05-07

--
[/php]
日期和时间部分已终结,下面是数值格式化部分~~
未完待续~~~

[ 本帖最后由 junsansi 于 2007-12-19 17:30 编辑 ]

使用道具 举报

回复
论坛徽章:
33
红孩儿
日期:2006-04-13 07:34:50ITPUB十周年纪念徽章
日期:2011-11-01 16:19:412012新春纪念徽章
日期:2012-01-04 11:49:54玉石琵琶
日期:2012-02-21 15:04:38奥运会纪念徽章:射击
日期:2012-08-10 11:01:09ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07
发表于 2007-12-19 13:21 | 显示全部楼层
很好 ~

使用道具 举报

回复
论坛徽章:
57
马上加薪
日期:2014-02-19 11:55:142011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:01ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522010系统架构师大会纪念
日期:2010-09-03 16:39:572010数据库技术大会纪念徽章
日期:2010-05-13 10:04:272010新春纪念徽章
日期:2010-03-01 11:21:02
发表于 2007-12-19 13:27 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
87
2015年新春福章
日期:2015-03-06 11:58:182010广州亚运会纪念徽章:轮滑
日期:2010-09-23 17:19:212010年世界杯参赛球队:乌拉圭
日期:2010-07-14 17:54:242010年世界杯参赛球队:美国
日期:2010-06-30 13:13:582010年世界杯参赛球队:墨西哥
日期:2010-06-25 12:49:452010年世界杯参赛球队:墨西哥
日期:2010-04-05 10:23:502010新春纪念徽章
日期:2010-03-01 11:06:232010新春纪念徽章
日期:2010-01-04 08:33:08生肖徽章2007版:龙
日期:2009-11-12 16:31:13参与WIN7挑战赛纪念
日期:2009-11-09 11:50:09
发表于 2007-12-19 13:32 | 显示全部楼层
呵呵
看來樓主很善于總結啊!

使用道具 举报

回复
论坛徽章:
21
红旗
日期:2013-09-30 15:26:01凯迪拉克
日期:2013-10-23 12:48:26比亚迪
日期:2013-11-01 09:19:01奔驰
日期:2013-12-13 09:27:30马上有对象
日期:2014-11-18 10:46:242015年新春福章
日期:2015-04-28 15:24:55慢羊羊
日期:2015-05-28 09:49:31
发表于 2007-12-19 14:06 | 显示全部楼层
值得三思

使用道具 举报

回复
论坛徽章:
0
发表于 2007-12-19 14:42 | 显示全部楼层
好贴
学习

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表