-- ww的算法為每年1月1日為第一周開始，date+6為每一周結(jié)尾
-- 例如20050101為第一周的第一天，而第一周的最后一天為20050101+6=20050107
-- 公式 每周第一天 ：date + 周 * 7 - 7
-- 每周最后一天：date + 周 * 7 - 1
不管怎么編排格式都會跑掉，真氣人 ~><~
=========================================================================
--日期計(jì)算，算第n周的第一天及最后一天是幾號。 by keynes 2005.04.29
=========================================================================
-- ww的算法為每年1月1日為第一周開始，date+6為每一周結(jié)尾
-- 例如20050101為第一周的第一天，而第一周的最后一天為20050101+6=20050107
-- 公式 每周第一天 ：date + 周 * 7 - 7
-- 每周最后一天：date + 周 * 7 - 1
-- 如果以ww格式為主，第1、17周的起迄如下
127.0.0.1:asdb:WF>select to_date(‘20050101‘,‘yyyymmdd‘) + 1*7-7,to_date(‘20050101‘,‘yyyymmdd‘) + 1*7-1 from dual;
TO_DATE(‘ TO_DATE(‘
--------- ---------
01-JAN-05 07-JAN-05
127.0.0.1:asdb:WF>select to_date(‘20050101‘,‘yyyymmdd‘) + 17*7-7,to_date(‘20050101‘,‘yyyymmdd‘) + 17*7-1 from dual;
TO_DATE(‘ TO_DATE(‘
--------- ---------
23-APR-05 29-APR-05
Elapsed: 00:00:00.00
-- 驗(yàn)證如下
127.0.0.1:asdb:WF>select to_char(to_date(‘20050422‘,‘yyyymmdd‘),‘ww‘) as weekn,to_char(to_date(‘20050423‘,‘yyyymmdd‘),‘ww‘) as week1,to_char(to_date(‘20050429‘,‘yyyymmdd‘),‘ww‘) as week2,to_char(to_date(‘20050430‘,‘yyyymmdd‘),‘ww‘) as weekn2 from dual;
WEEK WEEK WEEK WEEK
---- ---- ---- ----
16 17 17 18
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
-- iw的算法為星期一至星期日算一周，且每年的第一個星期一為第一周，
-- 例如20050101為星期六，所以用iw的算法是前年的53周，而20050103之后才是第一周的開始。
-- 公式 每周第一天 ：next_day(date) + 周 * 7 - 7
-- 每周最后一天：next_day(date) + 周 * 7 - 1
-- 如果以iw格式為主，第1、17周的起迄如下
127.0.0.1:asdb:WF>select next_day(to_date(‘20050101‘,‘yyyymmdd‘),‘MONDAY‘)+ 1 * 7 - 7 as first_day,next_day(to_date(‘20050101‘,‘yyyymmdd‘),‘MONDAY‘)+ 1 * 7 - 1 as last_day from dual;
FIRST_DAY LAST_DAY
--------- ---------
03-JAN-05 09-JAN-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
127.0.0.1:asdb:WF>select next_day(to_date(‘20050101‘,‘yyyymmdd‘),‘MONDAY‘)+ 17 * 7 - 7 as first_day,next_day(to_date(‘20050101‘,‘yyyymmdd‘),‘MONDAY‘)+ 17 * 7 - 1 as last_day from dual;
FIRST_DAY LAST_DAY
--------- ---------
25-APR-05 01-MAY-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
-- 驗(yàn)證如下
127.0.0.1:asdb:WF>select to_char(to_date(‘20050424‘,‘yyyymmdd‘),‘iw‘) as weekn,to_char(to_date(‘20050425‘,‘yyyymmdd‘),‘iw‘) as week1,to_char(to_date(‘20050501‘,‘yyyymmdd‘),‘iw‘) as week2,to_char(to_date(‘20050502‘,‘yyyymmdd‘),‘iw‘) as weekn2 from dual;
WEEK WEEK WEEK WEEK
---- ---- ---- ----
16 17 17 18
Elapsed: 00:00:00.00
其它：
--== 查今天是 "本月" 的第幾周
SELECT TO_CHAR(SYSDATE,‘WW‘) - TO_CHAR(TRUNC(SYSDATE,‘MM‘),‘WW‘) + 1 AS "weekOfMon" from dual;
或
SELECT TO_CHAR(SYSDATE,‘W‘) AS "weekOfMon" from dual;
--== 查今天是 "今年" 的第幾周
select to_char(sysdate,‘ww‘) from dual;
或
select to_char(sysdate,‘iw‘) from dual;
附注：
上文所提之iw及ww格式在doc內(nèi)解釋如下
IW = Week of year (1-52 or 1-53) based on the ISO standard
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.
參考文件：
Format Models：http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#34512
本篇是接續(xù)前一篇，因有朋友(allenc)要計(jì)算oracle的周數(shù)日期起迄，
所以測試了一下，
測試結(jié)果如下：
-- 特殊周數(shù)計(jì)算
-- 星期日到星期六為一周(與ww及iw算法不同)
-- 例2005年的第一周起迄是同一天，是20050101
-- 例2004年的第一周起為20040101迄為20040103
-- 計(jì)算第一周的天數(shù)
create or replace function fdf(p_date in date) return number
is
begin
-- 檢查是否傳入要計(jì)算那一年的一月一日
if to_char(to_char(p_date,‘ddd‘)) <> ‘001‘ then
return null;
end if;
--如果第一周的第一天剛好也是最后一天時，傳回1
if to_char(p_date,‘d‘) <> ‘7‘ then
return (next_day(p_date,‘SATURDAY‘) - p_date +1);
else
return 1;
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
計(jì)算公式：
起 ：decode(周,1,計(jì)算當(dāng)年的一月一日,計(jì)算當(dāng)年的一月一日 + (fdf(計(jì)算當(dāng)年的一月一日) + (周 - 2)*7)) as 起
迄(第一種算法)：decode(周,53,to_date(to_char(計(jì)算當(dāng)年的一月一日,‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),計(jì)算當(dāng)年的一月一日 + (fdf(計(jì)算當(dāng)年的一月一日) + (周-1)*7-1)) as 迄
迄(第二種算法)：decode(周,53,last_day(trunc(計(jì)算當(dāng)年的一月一日,‘mm‘)+340),計(jì)算當(dāng)年的一月一日 + (fdf(計(jì)算當(dāng)年的一月一日) + (周-1)*7-1)) as 迄
上面的參數(shù)部份，"計(jì)算當(dāng)年的一月一日"為date type，"周"為number type
傳回值一律是date型態(tài)
例如：
--2004年第1周
select decode(1,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (1 - 2)*7)) as s_week,decode(1,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (1-1)*7-1)) as e_week from dual;
--2004年第3周
select decode(3,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (3 - 2)*7)) as s_week,decode(3,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (3-1)*7-1)) as e_week from dual;
--2004年第53周
select decode(53,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (53 - 2)*7)) as s_week,decode(53,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (53-1)*7-1)) as e_week from dual;
--2005年第1周
select decode(1,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (1 - 2)*7)) as s_week,decode(1,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (1-1)*7-1)) as e_week from dual;
--2005年第3周
select decode(3,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (3 - 2)*7)) as s_week,decode(3,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (3-1)*7-1)) as e_week from dual;
--2005年第53周
select decode(53,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53 - 2)*7)) as s_week,decode(53,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53-1)*7-1)) as e_week from dual;
--2005年第53周方法2
select decode(53,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53 - 2)*7)) as s_week,decode(53,53,last_day(trunc(to_date(‘20050101‘,‘yyyymmdd‘),‘mm‘)+340),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53-1)*7-1)) as e_week from dual;
結(jié)果如下：
127.0.0.1:asdb:WF>--2004年第1周
127.0.0.1:asdb:WF>select decode(1,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (1 - 2)*7)) as s_week,decode(1,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (1-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
01-JAN-04 03-JAN-04
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>--2004年第3周
127.0.0.1:asdb:WF>select decode(3,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (3 - 2)*7)) as s_week,decode(3,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (3-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
11-JAN-04 17-JAN-04
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>--2004年第53周
127.0.0.1:asdb:WF>select decode(53,1,to_date(‘20040101‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (53 - 2)*7)) as s_week,decode(53,53,to_date(to_char(to_date(‘20040101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20040101‘,‘yyyymmdd‘) + (fdf(to_date(‘20040101‘,‘yyyymmdd‘)) + (53-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
26-DEC-04 31-DEC-04
Elapsed: 00:00:00.01
127.0.0.1:asdb:WF>
127.0.0.1:asdb:WF>--2005年第1周
127.0.0.1:asdb:WF>select decode(1,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (1 - 2)*7)) as s_week,decode(1,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (1-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
01-JAN-05 01-JAN-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>--2005年第3周
127.0.0.1:asdb:WF>select decode(3,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (3 - 2)*7)) as s_week,decode(3,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (3-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
09-JAN-05 15-JAN-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>--2005年第53周
127.0.0.1:asdb:WF>select decode(53,1,to_date(‘20050101‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53 - 2)*7)) as s_week,decode(53,53,to_date(to_char(to_date(‘20050101‘,‘yyyymmdd‘),‘yyyy‘)||‘12‘||‘31‘,‘yyyymmdd‘),to_date(‘20050101‘,‘yyyymmdd‘) + (fdf(to_date(‘20050101‘,‘yyyymmdd‘)) + (53-1)*7-1)) as e_week from dual;
S_WEEK E_WEEK
--------- ---------
25-DEC-05 31-DEC-05
Elapsed: 00:00:00.01
127.0.0.1:asdb:WF>
