oracle的周、月份、日历

oracle的周、月份、日历

1、周

/* 关键在于当年第一周的第一天和最后一天怎么算，后面只需加上7天就是一周，类推

--还有就是确定的是，一年总共只有53周

--Oracle中周日为第一天 ，周六为最后一天 */

SELECT LEVEL 周次,

    (Trunc(SYSDATE,'yyyy')-7) + (7-To_Char(Trunc(SYSDATE,'yyyy'),'d')+1) + (LEVEL-1)*7  当周第一天,

    (Trunc(SYSDATE,'yyyy')-7) + (7-To_Char(Trunc(SYSDATE,'yyyy'),'d')+1) + (LEVEL-1)*7+6 当周最后一天

FROM dual

CONNECT BY LEVEL<=53;

2、月份

--当年每个月的起止时间

 select aa as monNum, to_date(mon,'yyyymmdd') as firstDay,LAST_DAY(to_date(mon,'yyyymmdd')) as lastDay

   from (

         select to_char(sysdate,'yyyy')||to_char(aa,'09')||'01' mon ,aa

           from (

                  select 1 aa from dual union

                  select 2 aa from dual union

                  select 3 aa from dual union

                  select 4 aa from dual union

                  select 5 aa from dual union

                  select 6 aa from dual union

                  select 7 aa from dual union

                  select 8 aa from dual union

                  select 9 aa from dual union

                  select 10 aa from dual union

                  select 11 aa from dual union

                  select 12 aa from dual

                )

        );

3、日历

--from:http://www.itpub.net/469543.html   

 select case

         when (new_yweek = min(new_yweek)

               over(partition by mon order by new_yweek)) then

          mon_name

         else

          null

       end as month,

       new_yweek as yweek,

       row_number() over(partition by mon order by new_yweek) as mweek,

       sum(decode(wday, '1', mday, null)) as sun,

       sum(decode(wday, '2', mday, null)) as mon,

       sum(decode(wday, '3', mday, null)) as tue,

       sum(decode(wday, '4', mday, null)) as wed,

       sum(decode(wday, '5', mday, null)) as thu,

       sum(decode(wday, '6', mday, null)) as fri,

       sum(decode(wday, '7', mday, null)) as sat

  from (select dayofyear as everyday,

               to_char(dayofyear, 'mm') as mon,

               to_char(dayofyear, 'Month') as mon_name,

               to_char(dayofyear, 'w') as mweek,

               to_char(dayofyear, 'ww') as yweek,

               case

                 when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and

                      (to_char(dayofyear, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd'))

                  then

                    to_char(to_char(dayofyear, 'ww') + 1, 'fm00')

                  else

                    to_char(dayofyear, 'ww')

               end as new_yweek,

               to_char(dayofyear, 'd') as wday,

               to_char(dayofyear, 'dd') as mday

          from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear

                  from dual

                connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')))

group by mon, mon_name, new_yweek  

 

4、应用

--每周的上报情况 ，由于跨年情况存在，故在第几周前加年份缀，如 [2010]-7 ；

select weekNumCur,   sum(上报数) from (

select eventID ,上报数,'['||to_char(createtime,'yyyy')||']-'||to_number(to_char(createtime,'ww'))  as weekNumCur  , createtime

from statuser.tostat

where createtime between to_date('2010-12-01','yyyy-mm-dd') and to_date('2011-01-10','yyyy-mm-dd')+1 )

group by weekNumCur 

order by weekNumCur;

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

时间（周）上报数

[2010]-50  1

[2010]-51  16

[2010]-52  2

[2011]-1  6