Oracleto_char（）函数相关详解
（⼀）Oracle to_char()函数中的IW,WW 周别显⽰
1）ww的算法为每年1⽉1⽇为第⼀周开始，date+6为每⼀周结尾
例如20050101为第⼀周的第⼀天，⽽第⼀周的最后⼀天为20050101+6=20050107
公式每周第⼀天：date + 周 * 7 - 7
每周最后⼀天：date + 周 * 7 - 1
2) iw算法为⾃然周，即星期⼀到星期⽇为⼀周。且每年的第⼀个星期天为第⼀周，这样就会把去年的最后⼏天当成今年的第⼀周⽇期
1 select to_char(date'2015-1-1', 'yyyyww') from dual;
2 -- 201501
3 select to_char(date'2015-1-1', 'yyyyiw') from dual;
4 -- 201501
5 select to_char(date'2015-1-5', 'yyyyww') from dual;
6 201501
7 select to_char(date'2015-1-5', 'yyyyiw') from dual;
8 201502
9 select to_char(date'2014-12-31', 'yyyyww') from dual;
10 201453
11 select to_char(date'2014-12-31', 'yyyyiw') from dual;
12 201401 特别注意这⾥，如果做统计，很容易和真正的201401周数据合并了，其实应该是201501周
13 select to_char(date'2014-12-28', 'yyyyww') from dual;
14 201452
15 select to_char(date'2014-12-28', 'yyyyiw') from dual;
16 201452
推荐使⽤
trunc(date'','iw') --获取每个⽇期的周⼀，通过周⼀⽇期来排序统计，这样解决跨年的⾃然周问题
----------------------------------------------------------------------------------------------------------------------------
（⼆）
DDD是该天在⼀年内的第多少天，d是在⼀周内第⼏天，dd是⼀个⽉内的
DY ：Day of week abbreviated Mon, Tue, Fri
DAY ：Day of week spelled out Monday, Tuesday, Friday
D ：Day of week (1–7) 1,2,3,4,5,6,7－－注意：每星期的第1天是”星期⽇“
DD ：Day of month (1–31) 1,2,3,4 (31)
DDD ：Day of year (1–366) 1,2,3,4 (366)
-
---------------------------------------------------------------------------------------------------------------------------
The following are number examples for the to_char function.
to_char(1210.73, '9999.9')would return '1210.7'
to_char(1210.73, '9,999.99')would return '1,210.73'
to_char(1210.73, '$9,999.00')would return '$1,210.73'
to_char(21, '000099')would return '000021'
The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.
Parameter Explanation
YEAR Year, spelled out
YYYY4-digit year
YYY
YY
Last 3, 2, or 1 digit(s) of year.
Y
IYY
IY
Last 3, 2, or 1 digit(s) of ISO year.
I
I
IYYY4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12Hour of day (1-12).
HH24Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.
The following are date examples for the to_char function.
to_char(sysdate, 'yyyy/mm/dd');would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');would return 'Jul 9th, 2003'
You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.
oracle四舍五入to_char(sysdate, 'FMMonth DD, YYYY');would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');would return 'Jul 9th, 2003'
The zeros have been suppressed so that the day component shows as "9" as opposed to "09".
函数to_char转化数字型指定⼩数点位数的⽤法
to_char，函数功能，就是将数值型或者⽇期型转化为字符型。
⽐如最简单的应⽤：
/*1.0123--->'1.0123'*/
Select TO_CHAR(1.0123) FROM DUAL
/*123--->'123'*/
Select TO_CHAR(123) FROM DUAL
接下来再看看下⾯：
/*0.123 ---> '.123' */
SELEC TO_CHAR(0.123) FROM DUAL
上⾯的结果 '.123' 在⼤多数情况下都不是我们想要的结果，我们想要的应该是 '0.123'。
我们来看⼀下to_char函数的具体⽤法：
TO_CHAR ( n [, fmt [, 'nlsparam']] )
该函数将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'指定由数值格式的元素返回的字符，包括: ．⼩数点字符
．组分隔符
．本地钱币符号
．国际钱币符号
变元的形式为：
'NLS_NUMERIC_CHARACTERS="dg" NLS_CURRENCY="tcxt" NLS_ISO_CURRENCY=territory'
其中d为⼩数点字符,g为组分隔符。
例 :TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')=NUD017,145
通过上⾯的了解，再查看fmt的⼀些格式，我们可以⽤以下表达式得到'0.123'的值：
/*0.123 ---> ' 0.123' */
Select TO_CHAR(0.123,'0.999') FROM DUAL
/*100.12 ---> '>#' */
Select TO_CHAR(100.12,'0.999') FROM DUAL
/*1.12 ---> ' 1.120' */
Select TO_CHAR(1.12,'0.999') FROM DUAL
' 0.123'是出来了，可是前⾯⼜多了⼀个空格。
对于 100.12 的值却是>#，以及'1.12'的值变成了 '1.120'。
我们重新确定⼀个新的需求：
1、去空格
2、⼩数点最多4位，最少保留2位。
1--->'1.00'；1.1--->'1.00'；1.12-->'1.12'；1.1234--->'1.1234'；
1.12345--->'1.1235'
最终实现如下：
/*
FM ：除空格
9999999.0099：允许⼩数点左边最⼤正数为7位，⼩数点右边最少2位，最多4位，且在第5位进⾏四舍五⼊
*/
Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL
to_char函数特殊⽤法
to_char(sysdate,'d') 每周第⼏天
to_char(sysdate,'dd') 每⽉第⼏天
to_char(sysdate,'ddd') 每年第⼏天
to_char(sysdate,'ww') 每年第⼏周
to_char(sysdate,'mm') 每年第⼏⽉
to_char(sysdate,'q') 每年第⼏季
to_char(sysdate,'yyyy') 年
⽐如要某个时间为每周第⼏天就可以
SQL> select to_char(to_date('20070101','yyyymmdd'),'d') from dual;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
Oracle字段保留2两位小数点number类型
« 上一篇
oracle select里引用字段计算结果
下一篇 »
发表评论