Oracle 获得某日期属于该前月中的第几个自然周

select to_week_by_month(to_date('2010-1-31', 'yyyy-MM-dd'))
  from bak_member
where rownum = 1  //输出:5

SQL代码如下:
create or replace function to_week_by_month(sdate in date) return number is
  Result number;
  day    number;
  para   number;
begin

  select to_number(to_char(sdate, 'dd'))
    into day
    from dual;

  select (7 - to_number(to_char(TRUNC(TO_DATE(to_char(sdate,
                                                      'yyyymmdd'),
                                              'YYYYMMDD'),
                                      'mm'),
                                'D')) + 2)
    into para
    from dual;
  if para = 8 then--1号为周日则为第一周
    Result := 1;
    if day > 1 then
       if day <= para then
         Result := 2;
       else
         select to_number(to_char(sdate - para, 'w'))+2 week into Result from dual;
       end if;
    end if;
  else
    if day <= para then
      Result := 1;
    else
      select to_number(to_char(sdate - para, 'w'))+1 week into Result from dual;
    end if;
  end if;

  return Result;
end to_week_by_month;
/