http://hi.baidu.com/fishman0923/blog/item/28879dc1a869a5df38db49d2.html
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
