自然年周计算错误，寻nGX20080110

Question

刚这个函数，执行2012年时出现问题，20120107星期六 本 是第2周，结果是第一周，2012013星期六本是年第3周，结果是第2周，同理，2012年月份星期六的显示都出现问题
我把-- 0 for start from Sunday改成了-- 1 for start from Sunday
2010结果正确，没有这样的问题啊，晕了create or replace function GetWeekNumber(p_date date, p_sunday_start number)
return number
is
  day_first_day_of_year number := to_number(to_char(trunc(p_date,'yyyy'), 'd'));
  day_p_date number := to_number(to_char(p_date, 'd'));
  retval number := to_number(to_char(p_date,'ww'));
begin
  -- 1 for start from Sunday
  if p_sunday_start != 1 then -- non zera for start from Monday
    day_first_day_of_year := day_first_day_of_year - 1;
    day_p_date := day_p_date - 1;
  end if;
  
  if day_p_date = 1 then
    day_p_date := 7;
  end if;
  
  if day_first_day_of_year = 1 then
    day_first_day_of_year := 7;
  end if;
  
  if day_p_date < day_first_day_of_year then
    retval := retval + 1;
  end if;
  
  return retval;
end ;

Answer 1

试试这个create or replace function GetWeekNumber(p_date date)
return number
is
  retval number(2);
begin
  if to_char(trunc(p_date,'y'),'d')+trunc(p_date)-trunc(p_date,'y')<=8 then
    retval:=1;
  else
    select decode(to_char(trunc(p_date,'yyyy'),'d'),2,to_char(p_date,'WW'),1,to_char(p_date-1,'WW')+1,to_char(p_date-(9-to_char(trunc(p_date,'yyyy'),'d')),'WW')+1) 
      into retval
    from dual;
  end if;  
  return retval;
end ;

Answer 2

SQL> select GetWeekNumber(to_date('2010-01-01','yyyy-mm-dd')) from dual;
 
GETWEEKNUMBER(TO_DATE('2010-01
------------------------------
                             1
 
SQL> select GetWeekNumber(to_date('2010-01-02','yyyy-mm-dd')) from dual;
 
GETWEEKNUMBER(TO_DATE('2010-01
------------------------------
                             1
 
SQL> select GetWeekNumber(to_date('2010-01-03','yyyy-mm-dd')) from dual;
 
GETWEEKNUMBER(TO_DATE('2010-01
------------------------------
                             1
 
SQL> select GetWeekNumber(to_date('2010-01-04','yyyy-mm-dd')) from dual;
 
GETWEEKNUMBER(TO_DATE('2010-01
------------------------------
                             2
 
SQL> select GetWeekNumber(to_date('2010-09-06','yyyy-mm-dd')) from dual;
 
GETWEEKNUMBER(TO_DATE('2010-09
------------------------------
                            37
 
SQL> 

Answer 3

写正确还真不容易.上面那个还是有问题.
create or replace function GetWeekNumber(p_date date)
return number
is
  retval number(2);
begin
  if (to_char(trunc(p_date,'y'),'d')>=2 and to_char(trunc(p_date,'y'),'d')+trunc(p_date)-trunc(p_date,'y')<=8) or (to_char(trunc(p_date,'y'),'d')=1 and trunc(p_date)=trunc(p_date,'y')) then
    retval:=1;
  else
    select decode(to_char(trunc(p_date,'yyyy'),'d'),2,to_char(p_date,'WW'),1,to_char(p_date-1,'WW')+1,to_char(p_date-(9-to_char(trunc(p_date,'yyyy'),'d')),'WW')+1)
      into retval
    from dual;
  end if;
  return retval;
end ;

Answer 4

--昨天回去写了个，家里没网所以现在发上来。你的一周是从星期一开始的是不CREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 or instr(v_num1,'.')<=0 then
select round(v_num1)+1 into v_weeks from dual;
end if;
return v_weeks;
else
select round((trunc(dt)-trunc(dt,'year'))/7) into v_num from dual;
v_num1:=v_num;
v_weeks:=v_num1;
end if;
return v_weeks;
end;

Answer 5

getweeknumber的第二個參數是用來決定星期幾是開始的，如下傳入非0就是從星期一開始，結果沒問題。select to_date('20120101','yyyymmdd') + (level - 1) d,
to_char(to_date('20120101','yyyymmdd')  + (level - 1), 'day') day,
getweeknumber(to_date('20120101','yyyymmdd') + (level - 1),1) week#
from dual
connect by level < 30;D         DAY                                                         WEEK#
--------- ------------------------------------------------------ ----------
01-JAN-12 sunday                                                          1
02-JAN-12 monday                                                          2
03-JAN-12 tuesday                                                         2
04-JAN-12 wednesday                                                       2
05-JAN-12 thursday                                                        2
06-JAN-12 friday                                                          2
07-JAN-12 saturday                                                        2
08-JAN-12 sunday                                                          2
09-JAN-12 monday                                                          3
10-JAN-12 tuesday                                                         3
11-JAN-12 wednesday                                                       3
12-JAN-12 thursday                                                        3
13-JAN-12 friday                                                          3
14-JAN-12 saturday                                                        3
15-JAN-12 sunday                                                          3
16-JAN-12 monday                                                          4
17-JAN-12 tuesday                                                         4
18-JAN-12 wednesday                                                       4
19-JAN-12 thursday                                                        4
20-JAN-12 friday                                                          4
21-JAN-12 saturday                                                        4D         DAY                                                         WEEK#
--------- ------------------------------------------------------ ----------
22-JAN-12 sunday                                                          4
23-JAN-12 monday                                                          5
24-JAN-12 tuesday                                                         5
25-JAN-12 wednesday                                                       5
26-JAN-12 thursday                                                        5
27-JAN-12 friday                                                          5
28-JAN-12 saturday                                                        5
29-JAN-12 sunday                                                          5

Answer 6

select FUN_WEEKS(to_date('20080101','YYYYMMDD')) from dual;
--结果是0
select FUN_WEEKS(to_date('20180101','YYYYMMDD')) from dual;
--结果是0
2009-2010没看到什么问题

Answer 7

CREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 or instr(v_num1,'.')<=0 then
select round(v_num1)+1 into v_weeks from dual;
end if;
return v_weeks;
else
select round((trunc(dt)-trunc(dt,'year'))/7)+1 into v_num from dual;
v_num1:=v_num;
v_weeks:=v_num1;
end if;
return v_weeks;
end;--没有考虑仔细，提醒了我SQL> select fun_weeks(to_date('20180101','YYYYMMDD')) from dual
  2  /FUN_WEEKS(TO_DATE('20180101','YYYYMMDD'))
-----------------------------------------
                                        1

Answer 8

--刚刚完善年份的第一天为星期一的漏洞了，不会出现那问题了 ，试试这个。不再写了，不知为什么不用西方的一个星期从星期日开始，这样会好许多的
CREATE OR REPLACE FUNCTION FUN_WEEKS (dt date) return number
as
v_num number;
v_num1 number;
v_weeks number:=0;
begin
if to_char(trunc(dt,'year'),'day')<>'星期一' then
select (trunc(dt)-trunc(dt,'year')-decode(to_char(trunc(dt,'year'),'day'),'星期二',5,'星期三',4
,'星期四',3,'星期五',2,'星期六',1,'星期日',0))/7 into v_num from dual;
v_num1:=v_num;
if v_num1>0 and instr(v_num1,'.')>0 then
if substr(v_num1,instr(v_num1,'.')+1,1)>4 then
select round(v_num1)+1 into v_weeks from dual;
else
select round(v_num1)+2 into v_weeks from dual;
end if;
elsif v_num1<0 or instr(v_num1,'.')<=0 then
select round(v_num1)+1 into v_weeks from dual;
end if;
else
select (trunc(dt)-trunc(dt,'year'))/7 into v_num from dual;
if (v_num=0 or (instr(v_num,'.')>0 and substr(v_num,instr(v_num,'.')+1,1)<5) or instr(v_num,'.')=0) then
v_num1:=round(v_num)+1;
v_weeks:=v_num1;
elsif (v_num<>0 or substr(v_num,instr(v_num,'.')+1,1)>4) then
v_num1:=round(v_num);
v_weeks:=v_num1;
end if;
end if;
return v_weeks;
end;
/

Answer 9

一周从周一开始，计算给定日期为第几周？
试试这样吧
WITH TEST_DATA AS
 (SELECT DATE '2009-1-1' - 1 + ROWNUM A FROM DUAL CONNECT BY ROWNUM < 2000)
 
SELECT A "DATE", TO_CHAR(A, 'day') "DAY", CEIL((TO_CHAR(A, 'ddd') + MOD(TO_CHAR(TRUNC(A, 'yyyy'), 'd') + 5, 7)) / 7) "WEEK"
  FROM TEST_DATA

Answer 10

--最终版本
CREATE OR REPLACE FUNCTION UDF_WEEKOFYEAR(dt date) return char as
  v_num   number;
  v_weeks number := 0;
begin
  if to_char(trunc(dt, 'year'), 'day') <> '星期一' then--如当年的第一天不是星期一
    --获取当天和本年第一天的天数差，减去第一个周一前剩余的天数，最后除以7
    select (trunc(dt) - trunc(dt, 'year') -
           decode(to_char(trunc(dt, 'year'), 'day'),
                   '星期二',
                   6,
                   '星期三',
                   5,
                   '星期四',
                   4,
                   '星期五',
                   3,
                   '星期六',
                   2,
                   '星期日',
                   1)) / 7
      into v_num
      from dual;
    if v_num >= 0 then --如除数大于0，则直接向下取整+2
      v_weeks:= floor(v_num)+2; 
     elsif v_num < 0 then--如小于0，则为第一周
      v_weeks:=1;
    end if;
  else --如当年的第一天是星期一，取当天和本年第一天的天数差，除以7，向下取整+1
      v_weeks:= floor((trunc(dt) - trunc(dt, 'year'))/7)+1; 
  end if;
  --return v_weeks;
  return(case when v_weeks <= 9 then '0'||to_char(v_weeks) else to_char(v_weeks) end);
end;非常谢谢各位