求Oracle的sql 给定某一年(如2011),该年的第几周(如20周),求一个SQL,已知年和周,得到起止时间 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select * from (select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') sdate from dualconnect by rownum <= 366)where to_char(to_date(sdate,'yyyy-mm-dd'),'WW')=20 sdate ---------------------1 2011-05-142 2011-05-153 2011-05-164 2011-05-175 2011-05-186 2011-05-197 2011-05-20 上面是根据年和周获取那周的日期这个是获取周的起始日期select max(sdate) m_date,min(sdate) n_date from (select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate from dualconnect by rownum <= 365)where to_char(to_date(sdate,'yyyy-mm-dd'),'WW')=20 m_date n_date-------------------------------------1 2011-05-20 2011-05-14 SELECT trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*20, trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*20 + 6 from dual; 修正下SELECT trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*(20-1), trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*(20-1) + 6 from dual; select to_date(20110101,'YYYYMMDD') +(20-1)*7,to_date(20110101,'YYYYMMDD') +20*7-1 from dual select to_date(to_char(trunc(sysdate,'yyyy'),'yyyymmdd'),'yyyymmdd')+19*7+1, to_date(to_char(trunc(sysdate,'yyyy'),'yyyymmdd'),'yyyymmdd')+20*7 from dual; 遇到一个oracle问题, 菜鸟问个问题(关于删除表中大量数据) ORACLE 如何读取大字段的? 一个select语句产生的锁 sqlplus中有没有这样的语句? 关于SQL PLUS不能查询已经存在的表的问题? 在线等待,数据库为什么不能做任何修改了 数据库倒入,为什么不行? 保存数据时出错: 求助下载Developer 2000 关于两层游标的用法的疑问 本人在安装ORACLE 11g时遇到个问题:
select * from (
select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') sdate
from dual
connect by rownum <= 366)
where to_char(to_date(sdate,'yyyy-mm-dd'),'WW')=20
sdate
---------------------
1 2011-05-14
2 2011-05-15
3 2011-05-16
4 2011-05-17
5 2011-05-18
6 2011-05-19
7 2011-05-20
select to_char(to_date('2011'||'-01', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate
from dual
connect by rownum <= 365)
where to_char(to_date(sdate,'yyyy-mm-dd'),'WW')=20 m_date n_date
-------------------------------------
1 2011-05-20 2011-05-14
trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*20 + 6
from dual;
SELECT trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*(20-1),
trunc(to_date('2011'||'0101','yyyymmdd'), 'day') + 1 + 7*(20-1) + 6
from dual;
from dual
to_date(to_char(trunc(sysdate,'yyyy'),'yyyymmdd'),'yyyymmdd')+20*7 from dual;