oracle获得日期自然周-函数

minghe_zy

浏览: 125888 次
性别:
来自: 北京

最近访客更多访客>>

僵小鱼

xn792474

hnqq

小博爷

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (37)

社区版块

存档分类

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;