oralce sql 创建指定时间段内的日历信息,oralcesql
创建 开始时间是2010 截止时间是2100的日历表数据。
create table temp_calendar as select
min(to_char(day, 'yyyymm')) month,to_char(min(decode(weekday, 1, day)) ,'dd') w_7,
to_char(min(decode(weekday, 2, day)),'dd') w_1,
to_char(min(decode(weekday, 3, day)),'dd') w_2,
to_char(min(decode(weekday, 4, day)),'dd') w_3,
to_char(min(decode(weekday, 5, day)),'dd') w_4,
to_char(min(decode(weekday, 6, day)),'dd') w_5,
to_char(min(decode(weekday, 7, day)),'dd') w_6
from (select trunc(day,'yyyy') year,
day,
month,
decode(sign(rn - weekday), 1, week + 1, week) week,
weekday,
rn
from (select day,
to_char(day, 'mm') month,
to_char(day, 'w') week,
to_char(day, 'd') weekday,
row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn
from (select trunc(to_date('2008','yyyy'), 'yyyy') + level - 1 day
from dual
connect by rownum <= trunc(to_date('2101','yyyy'),'yyyy')-trunc(to_date('2008','yyyy'),'yyyy')))) a
group by a.year,a.month, a.week
order by a.year,a.month, a.week;
select * from hr_info where to_char(comdate,'yyyy-mm-dd') between '2009-01-01' and '2010-01-01'
select IMSI,bytes from session where dtcol between to_date('2012-11-01 23:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2012-11-02 07:00:00','yyyy-mm-dd hh24:mi:ss')
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。