FUN_SYS_GET_NUM_WEEK(日期在第几周)

来源：互联网发布：程序员招聘网站编辑：程序博客网时间：2024/04/29 02:43

CREATE OR REPLACE FUNCTION LBI_SYS_VDF.FUN_SYS_GET_NUM_WEEK (vv_date in varchar2,
vv_week_start_flag in varchar2)

return number is -- created by liuzhiren
    vv_day_flag varchar2(4);
    vv_last_day_flag varchar2(4);
    vd_first_day date;
    vd_date date;
    vd_last_day date;
    vi_num_week varchar2(6);
    vv_year varchar2(4);
    vv_next_year varchar2(4);
    --vv_endweek varchar2(8);
    --vi_position integer;
begin
    vd_first_day := trunc(to_date(vv_date,'yyyymmdd'),'yyyy');
    vd_last_day := to_date(substr(vv_date,1,4)||'1231','yyyymmdd');
    vd_date := to_date(vv_date,'yyyymmdd');
    vv_year := substr(vv_date,1,4);
    vv_next_year := to_char(to_number(substr(vv_date,1,4))+1);
    vv_day_flag := to_char(vd_first_day,'d');
    vv_last_day_flag := to_char(vd_last_day,'d');
    --vv_week_start_flag = '0' means the first day of week is sunday
    if vv_week_start_flag = '0' then
      if vv_day_flag = '1' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day + 1 )/7)),2,'0');
      elsif vv_day_flag = '2' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day - 5)/7) + 1),2,'0');
      elsif vv_day_flag = '3' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day - 4)/7) + 1),2,'0');
      elsif vv_day_flag = '4' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day - 3)/7) + 1),2,'0');
      elsif vv_day_flag = '5' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day - 2)/7) + 1),2,'0');
      elsif vv_day_flag = '6' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day - 1)/7) + 1),2,'0');
      elsif vv_day_flag = '7' then
        vi_num_week := vv_year || lpad(to_char(ceil((vd_date - vd_first_day)/7) + 1),2,'0');
      end if;
      if vv_last_day_flag <> '7' and vd_date >= vd_last_day - to_number(vv_last_day_flag) + 1 then
        vi_num_week := vv_next_year || '01';
      end if;

0 0