ITPub博客

to_char取周数的算法

原创 IT综合 作者:xsb 时间:2009-09-27 14:41:01 0 删除 编辑

“周数”的计算规则如下:
算法一:iw的算法为星期一至星期日算一周,且每年的第一个星期一为第一周
即:IW = Week of year (1-52 or 1-53) based on the ISO standard
SQL函数的取值是:
SELECT to_char(SYSDATE, 'iw') FROM dual;
注意有特殊的地方:如果一年当中第52周别之后至当年的12月31日之间,还有大于或等于4天的话,则定为当年的第53周,否则剩余这些天数被归为下一年的第1周;如果在不足52周别的话,则以下一年的时间来补;
比如20090101是周四,今年第一个周一是20090105,结果是第一个周一已经算是第二周了!
程序处理起来很复杂!

算法二:ww的算法为每年的1月1日作为当年的第一周的第一天(不管当年的1月1日是星期几);
即: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.
SQL函数的取值是:
SELECT to_char(SYSDATE, 'ww') FROM dual;
这个算法与国人习惯也不同!但算法很简单,可以变通为我们习惯的自然周算法(第一周和最后一周可能不足7天)。

[@more@]

很多时间我们需要用Oracle提供的to_char()函数来取得某个日期是属于今年的第几周.例如:TO_CHAR(TO_DATE('20090112','YYYYMMDD'), 'WW') ,得到的结果是02,就是第2?可看看日历上,2009年1月12日第三周的周一.为什么会有这样的问题呢?
由于Oracle在to_char()函数计算一年中的第几周是从该年的1月1日开始,7天为1周来计算的,所以2009年的1月12日,就是第2周.
为了得到自然周,我们需要为该日期加上该年的1月1日所在周被忽略掉的那些天数,然后再用TO_CHAR( )函数,就可以取得自然周了。
但当1月1日为周日时,用TO_DATE(' ', 'D')计算出它是该周的第1天,实际上这1天已是本年的第一周了,所以需要用一个DECODE将其置为第8天,然后再计算被忽略掉的天数,才能得到正确的结果。
所以,可以采用下面的SQL语句,仍然以2009年1月12日为例:
SELECT to_char(to_date('20090112', 'YYYYMMDD') +
to_number(decode(to_char(trunc(to_date('20090112', 'YYYYMMDD'), 'YYYY'), 'D'),
'1',
'8',
to_char(trunc(to_date('20090112', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2,
'WW')
FROM dual;

P.S. to_date('','D')是取该日期为一周内的第几天,从周日开始,周日为1.所以如果1月1日为周日,那么1月2日就应该是第2周.

上面的SQL语句,当日期为2009年12月31日时,计算出的结果就是01,这是什么原因呢?

因为我们为日期加上被忽略的天数时,可能造成年末的日期跨年,成为下一年的日期,这样再用TO_CHAR( )函数,取得的周就成了01。所以我们需要判断当日期跨年时就置为年末的最后一天,从而取得正确的周。

所以,最终采用下面的SQL语句,就能够得到正确的自然周了,以2009年12月31日为例:

SELECT to_char(decode(sign((to_date('20091231', 'YYYYMMDD') +
to_number(decode(to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'),
'1',
'8',
to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2) -
last_day(to_date('20091231', 'YYYYMMDD'))),
1,
last_day(to_date('20091231', 'YYYYMMDD')),
(to_date('20091231', 'YYYYMMDD') +
to_number(decode(to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'),
'1',
'8',
to_char(trunc(to_date('20091231', 'YYYYMMDD'), 'YYYY'), 'D'))) - 2)),
'WW')
FROM dual;

另外附上用于日期和时间的Format:

FORMAT 描述
HH 一天的小时数 (01-12)
HH12 一天的小时数 (01-12)
HH24 一天的小时数 (00-23)
MI 分钟 (00-59)
SS 秒 (00-59)
MS 毫秒 (000-999)
US 微秒 (000000-999999)
SSSS 午夜后的秒 (0-86399)
AM 或 A.M. 或 PM 或 P.M. 正午标识(大写)
am 或 a.m. 或 pm 或 p.m. 正午标识(小写)
Y,YYY 带逗号的年(4 和更多位)
YYYY 年(4和更多位)
YYY 年的后三位
YY 年的后两位
Y 年的最后一位
IYYY ISO 年(4位或更多位)
IYY ISO 年的最后 3 位
IY ISO 年的最后 2 位
I ISO 年的最后一位
BC 或 B.C. 或 AD 或 A.D. 纪元标识(大写)
bc 或 b.c. 或 ad 或 a.d. 纪元标识(小写)
MONTH 全长大写月份名(空白填充为9字符)
Month 全长混合大小写月份名(空白填充为9字符)
month 全长小写月份名(空白填充为9字符)
MON 大写缩写月份名(3字符)
Mon 缩写混合大小写月份名(3字符)
mon 小写缩写月份名(3字符)
MM 月份号(01-12)
DAY 全长大写日期名(空白填充为9字符)
Day 全长混合大小写日期名(空白填充为9字符)
day 全长小写日期名(空白填充为9字符)
DY 缩写大写日期名(3字符)
Dy 缩写混合大小写日期名(3字符)
dy 缩写小写日期名(3字符)
DDD 一年里的日子(001-366)
DD 一个月里的日子(01-31)
D 一周里的日子(1-7;周日是1)
W 一个月里的周数(1-5)(第一周从该月第一天开始)
WW 一年里的周数(1-53)(第一周从该年的第一天开始)
IW ISO 一年里的周数(第一个星期四在第一周里)
CC 世纪(2 位)
J 儒略日(自公元前4712年1月1日来的天数)
Q 季度
RM 罗马数字的月份(I-XII;I=JAN)(大写)
rm 罗马数字的月份(I-XII;I=JAN)(小写)
TZ 时区名 (大写)
tz 时区名 (小写)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12402/viewspace-1027400/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    66
  • 访问量
    3081227