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