oracle获得日期自然周-函数

文章来源：http://lijun0349-163-com.iteye.com/blog/616318

 

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

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

select to_week_by_year(to_date('2010-1-4', 'yyyy-MM-dd'))
  from bak_member
where rownum = 2  //输出:1

函数如下:
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;