自然年周计算错误，求解

Question

查询结果：8月24号-8月31号都显示为第35周
                  9月1号-9月6号显示为第36周
事实上应该：是8月24号-8月30号显示第35周
                        8月31号-9月6号显示为第36周
另外：
20100322-20100331都显示为13周
20100401 -20100404 都显示为14周
也有问题create or replace function to_week_by_year(sdate in date) return number is
  Result number;
begin
  select to_char(decode(sign((to_date(to_char(sdate, 'yyyymmdd'),
                                      'yyyymmdd') +
                             to_number(decode(to_char(trunc(to_date(to_char(sdate,
                                                                             'yyyymmdd'),
                                                                     'yyyymmdd'),
                                                             'yyyy'),
                                                       'd'),
                                               '1',
                                               '8',
                                               to_char(trunc(to_date(to_char(sdate,
                                                                             'yyyymmdd'),
                                                                     'yyyymmdd'),
                                                             'yyyy'),
                                                       'd'))) - 2) -
                             last_day(to_date(to_char(sdate, 'yyyymmdd'),
                                              'yyyymmdd'))),
                        1,
                        last_day(to_date(to_char(sdate, 'yyyymmdd'),
                                         'yyyymmdd')),
                        (to_date(to_char(sdate, 'yyyymmdd'), 'yyyymmdd') +
                        to_number(decode(to_char(trunc(to_date(to_char(sdate,
                                                                        'yyyymmdd'),
                                                                'yyyymmdd'),
                                                        'yyyy'),
                                                  'd'),
                                          '1',
                                          '8',
                                          to_char(trunc(to_date(to_char(sdate,
                                                                        'yyyymmdd'),
                                                                'yyyymmdd'),
                                                        'yyyy'),
                                                  'd'))) - 2)),
                 'ww')
    into result
    from dual;  return Result;
end to_week_by_year;

Answer 1

select decode(to_char(trunc(sysdate,'yyyy'),'d'),2,to_char(sysdate,'WW'),1,to_char(sysdate-1,'WW')+1,to_char(sysdate-(9-to_char(trunc(sysdate,'yyyy'),'d')),'WW')+1) from dual;
sysdate所在的位置就是你的输入变量应该替换的位置.

Answer 2

y写多了,写一个y就可以了
SQL> select sysdate from dual;
 
SYSDATE
-----------
2010-9-6 14
 
SQL> select decode(to_char(trunc(sysdate,'y'),'d'),2,to_char(sysdate,'WW'),1,to_char(sysdate-1,'WW')+1,to_char(sysdate-(9-to_char(trunc(sysdate,'y'),'d')),'WW')+1) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE,'
----------------------------------------
37
 
SQL> select decode(to_char(trunc(sysdate-1,'y'),'d'),2,to_char(sysdate-1,'WW'),1,to_char(sysdate-1-1,'WW')+1,to_char(sysdate-1-(9-to_char(trunc(sysdate-1,'y'),'d')),'WW')+1) from dual;
 
DECODE(TO_CHAR(TRUNC(SYSDATE-1
----------------------------------------
36

Answer 3

看的真的你的目的不是直接返回某个日期是这一年的第多少周
select to_char(sysdate,'ww')  from dual;

Answer 4

这个跟楼主的要求不太符合.to_char的返回是直接用天数/7取ceil得到的.

Answer 5

CREATE OR REPLACE FUNCTION UDF_WEEKOFYEAR(V_DATE DATE) RETURN char is
    v_ret1 number;
    v_ret2 varchar2(2);
    v_n    number;
BEGIN    if TO_CHAR(LAST_DAY(add_months(V_DATE, -12)) + 1, 'iw') <> '01' then
        v_n := to_number(TO_CHAR(LAST_DAY(add_months(V_DATE, -12)) + 1, 'iw'));
    
        SELECT DECODE(TO_CHAR(V_DATE, 'MM'),
                       '01',
                       mod(TO_CHAR(V_DATE, 'iw'), v_n),
                       TO_CHAR(V_DATE, 'iw') + 1)
        into v_ret1
        from dual;
    else
        SELECT TO_CHAR(V_DATE, 'iw') into v_ret1 from dual;
    end if;
    v_ret2 := to_char(v_ret1);
    return(case when v_ret1 <= 9 then '0' || v_ret2 else v_ret2 end);
END;

Answer 6

select to_char(sysdate,'ww') from dual;这个不行，它是要这样取年周的，比如，1月1日星期五，那么这年的第一周只有1-3号三天，4号开始是第二周

Answer 7

樓主，雖然已經來晚了，但你的函數是不是有點問題：
select UDF_WEEKOFYEAR(to_date('20100101','yyyymmdd')) from dual;UDF_WEEKOFYEAR(TO_DATE('20100101','YYYYMMDD'))                                  
--------------------------------------------------------------------------------
03  我另外寫了一個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
  -- 0 for start from Sunday
  if p_sunday_start != 0 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 = 0 then
    day_p_date := 7;
  end if;
  
  if day_first_day_of_year = 0 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 8

恩,我也赞成这样处理,主要是根据1月1日是星期几来分开处理,如果是周一就直接用to_char(时间,'ww'),其他情况位移一下再用to_char然后+1

Answer 9

在过程里先判断下to_char(trunc(时间,'y'),'ww')+trunc(时间)-trunc(时间,'y'),如果小于9直接返回1,大于等于9再用上面的公式.

Answer 10

第一周的情况那个sql里没判断.如果用sql来写就太复杂了.

Answer 11

在过程里先判断下to_char(trunc(时间,'y'),'d')+trunc(时间)-trunc(时间,'y'),如果小于9直接返回1,大于等于9再用上面的公式.
上面写的有点问题.