问题如下:
使用如下两句SQL查询,结果分别为35和36,而事实上这两天在同一周
select TO_CHAR (TO_DATE ('20080901', 'yyyyMMdd'), 'ww') as Week from dual;
select TO_CHAR (TO_DATE ('20080902', 'yyyyMMdd'), 'ww') as Week from dual;
原因:
Oracle对周的计算很原始,直接计算今天距离1/1有几天,然后除以7得到周。
修改方式:
我和kevin写了个函数,手工推算周,可以解决这个问题
CREATE OR REPLACE
FUNCTION getnaturalweek (
date_ IN VARCHAR2 DEFAULT TO_CHAR (SYSDATE, 'YYYYMMDD')
)
RETURN VARCHAR2
IS
add_days NUMBER (8, 0);
total_days NUMBER (8, 0);
total_weeks NUMBER (8, 0);
weeks VARCHAR2 (2);
BEGIN
add_days :=
TO_NUMBER (TO_CHAR (TRUNC (TO_DATE (date_, 'YYYYMMDD'), 'YYYY'), 'D'))
- 1;
total_days :=
TO_NUMBER (TO_CHAR (TO_DATE (date_, 'YYYYMMDD'), 'ddd'))
+ add_days;
total_weeks := CEIL (total_days / 7);
weeks := TO_CHAR (total_weeks);
IF LENGTH (weeks) <= 1
THEN
weeks := '0' || weeks;
END IF;
RETURN weeks;
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-733352/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11411056/viewspace-733352/