应minitoy要求，关于minitoy年周函数测试结果

Dream_1986 2010-09-09 10:59:31

--函数如下

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;

---注明，我在我的日历函数t_day_populate_ce中调用了GetWeekNumber函数

SQL> execute t_day_populate_ce(2010);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2009);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2008);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2007);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2010);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2011);

 

PL/SQL procedure successfully completed



SQL> execute t_day_populate_ce(2011);

PL/SQL procedure successfully completed

SQL>

--测试语句，各年份依次测过

select count(1), t_day_week_of_year --年第几周

  from t_day

 where id between 20110101 and 20111231

 group by t_day_week_of_year

 order by t_day_week_of_year;

--测试结果

--好像不会贴图。。。。。囧

--所测年份没发现什么问题，呵呵

COUNT(1）T_DAY_WEEK_OF_YEAR

2	1

7	2

7	3

7	4

7	5

7	6

7	7

7	8

7	9

7	10

7	11

7	12

7	13

7	14

7	15

7	16

7	17

7	18

7	19

7	20

7	21

7	22

7	23

7	24

7	25

7	26

7	27

7	28

7	29

7	30

7	31

7	32

7	33

7	34

7	35

7	36

7	37

7	38

7	39

7	40

7	41

7	42

7	43

7	44

7	45

7	46

7	47

7	48

7	49

7	50

7	51

7	52

6	53

--最后附上我们老大修改nGX20080110后的，测试通过

--最终版本

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;

...全文

99 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

心中的彩虹 2010-09-09

打赏
举报

[Quote=引用 8 楼 wkc168 的回复:]
引用 4 楼 dream_1986 的回复:
注：关于wkc168 的fun_weeks函数bug

SQL code
--2008年1月1日测试结果为0
select fun_weeks(to_date('20080101','YYYYMMDD')) from dual；

等下在去看看呵呵
[/Quote]



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 and instr(v_num1,'.')>0 then

if substr(v_num1,instr(v_num1,'.')+1,1)>4 then

select round(abs(v_num1)) into v_weeks from dual;

else

select round(v_num1)+1 into v_weeks from dual;

end if;

elsif v_num1=0 then

select 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;

/



SQL> select fun_weeks(to_date('20080101','YYYYMMDD')) from dual

  2  /



FUN_WEEKS(TO_DATE('20080101','YYYYMMDD'))

-----------------------------------------

                                        1



已用时间:  00: 00: 00.01

心中的彩虹 2010-09-09

打赏
举报

[Quote=引用 4 楼 dream_1986 的回复:]
注：关于wkc168 的fun_weeks函数bug

SQL code
--2008年1月1日测试结果为0
select fun_weeks(to_date('20080101','YYYYMMDD')) from dual；
[/Quote]

等下在去看看呵呵

gelyon 2010-09-09

打赏
举报

恩不错大家共同进步学习！顶楼主啊！

minitoy 2010-09-09

打赏
举报

其实版版的那个最简洁,而且没有错误.

Dream_1986 2010-09-09

打赏
举报

[Quote=引用 3 楼 minitoy 的回复:]

哈,谢了
[/Quote]
应该的。。呵呵

Dream_1986 2010-09-09

打赏
举报

注：关于wkc168 的fun_weeks函数bug

--2008年1月1日测试结果为0

select fun_weeks(to_date('20080101','YYYYMMDD')) from dual；

minitoy 2010-09-09

打赏
举报

哈,谢了

心中的彩虹 2010-09-09

打赏
举报

[Quote=引用 1 楼 dream_1986 的回复:]
哦，我那改动的是wkc168的，谢谢其余各位友情参与！！！！
[/Quote]

就是喜欢楼主这样的大家都双赢了

Dream_1986 2010-09-09

打赏
举报

哦，我那改动的是wkc168的，谢谢其余各位友情参与！！！！

Dream_1986 2010-09-09

打赏
举报

[Quote=引用 11 楼 wkc168 的回复:]
SQL code



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 and instr(v_num1,'.')>0 then

if substr(v_num1,instr(v_num1,'.')+1,1)>4 then

select round(abs(v_num1)) into v_weeks from dual;

else

select round(v_num1)+1 into v_weeks from dual;

end if;

elsif v_num1=0 or instr(v_num1,'.')<=0 then        -----忽略了天数之差除以7为整数的情况

select 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;

/

--测试年份

SQL> execute t_day_populate_ce(2008);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2007);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2009);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2010);

 

PL/SQL procedure successfully completed

 

SQL> execute t_day_populate_ce(2018);

 

PL/SQL procedure successfully completed

--测试语句，以2008年为例，其他部分年份测过，无误

select to_char(to_date('20080101','YYYYMMDD'),'DAY'),fun_weeks(to_date('20080101','YYYYMMDD')) from dual;

--查询结果

星期二 ，1

--测试语句

select count(1), t_day_week_of_year

  from t_day

 where id between 20080101 and 20081231

 group by t_day_week_of_year

 order by t_day_week_of_year;

---测试结果

COUNT(1)	T_DAY_WEEK_OF_YEAR

6	1

7	2

7	3

7	4

7	5

7	6

7	7

7	8

7	9

7	10

7	11

7	12

7	13

7	14

7	15

7	16

7	17

7	18

7	19

7	20

7	21

7	22

7	23

7	24

7	25

7	26

7	27

7	28

7	29

7	30

7	31

7	32

7	33

7	34

7	35

7	36

7	37

7	38

7	39

7	40

7	41

7	42

7	43

7	44

7	45

7	46

7	47

7	48

7	49

7	50

7	51

7	52

3	53

恭喜。。。

心中的彩虹 2010-09-09

打赏
举报



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 and instr(v_num1,'.')>0 then

if substr(v_num1,instr(v_num1,'.')+1,1)>4 then

select round(abs(v_num1)) into v_weeks from dual;

else

select round(v_num1)+1 into v_weeks from dual;

end if;

elsif v_num1=0 or instr(v_num1,'.')<=0 then        -----忽略了天数之差除以7为整数的情况

select 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;

/



SQL> select fun_weeks(to_date('20080113','YYYYMMDD')) from dual;



FUN_WEEKS(TO_DATE('20080113','YYYYMMDD'))

-----------------------------------------

                                        2



  1* select fun_weeks(to_date('20080114','YYYYMMDD')) from dual

SQL> /



FUN_WEEKS(TO_DATE('20080114','YYYYMMDD'))

-----------------------------------------

                                        3

Dream_1986 2010-09-09

打赏
举报



--测试语句

select count(1), t_day_week_of_year

  from t_day

 where id between 20080101 and 20081231

 group by t_day_week_of_year

 order by t_day_week_of_year;

---另外，单独给出一个日期

--结果有误

select fun_weeks(to_date('20080113','YYYYMMDD')) from dual;

--结果是0

COUNT(1)	T_DAY_WEEK_OF_YEAR

51	0

6	1

6	2

6	3

6	4

6	5

6	6

6	7

6	8

6	9

6	10

6	11

6	12

6	13

6	14

6	15

6	16

6	17

6	18

6	19

6	20

6	21

6	22

6	23

6	24

6	25

6	26

6	27

6	28

6	29

6	30

6	31

6	32

6	33

6	34

6	35

6	36

6	37

6	38

6	39

6	40

6	41

6	42

6	43

6	44

6	45

6	46

6	47

6	48

6	49

6	50

6	51

6	52

3	53