开始时间 截止时间
2008-11-11 2008-11-222008-12-11 2008-12-22
table a startDate ,endDate
查询出 开始时间 ,截止时间,时间段内周六,周日的天数
startDate endDate weekendNo
2008-11-11 2008-11-222008-12-11 2008-12-22
table a startDate ,endDate
查询出 开始时间 ,截止时间,时间段内周六,周日的天数
startDate endDate weekendNo
WW為一年中的周
W為一個月中的周
D為星期几
DDD為一年中的第几天
http://topic.csdn.net/t/20020725/16/901838.html#
select count(*) 周六天数
from 表
where to_char(sysdate-1,'6') =6 and startDate>=xxx and endDate<=yyy
select count(*) from
(select to_date('20081101','yyyymmdd') +rownum-1 rq
from dual
connect by rownum<=to_date('20081122','yyyymmdd')-to_date('20081101','yyyymmdd'))
where to_char(rq,'d') in (1,7)
2 (select to_date('20081101','yyyymmdd') +rownum-1 rq
3 from dual
4 connect by rownum <=to_date('20081122','yyyymmdd')-to_date('20081101','yyyymmdd'))
5 where to_char(rq,'d') in (1,7);
from dual
*
ERROR 位于第 3 行:
ORA-01436: 用户数据中的 CONNECT BY 循环
SQL> show errors;
没有错误。
startDate endDate weekendNo2008-11-11 2008-12-11 2
这种方法是可行的。
就是把 时间段内的每一天构造出来,在取其周几 count出来
SQL> select a.startdate, a.enddate, count(da)
2 from (select to_date('2008-01-01', 'yyyy-mm-dd') + rownum - 1 da
3 from dual
4 connect by rownum <= (to_date('2008-12-31', 'yyyy-mm-dd') -
5 to_date('2008-01-01', 'yyyy-mm-dd') + 1)),
6 a
7 where da between a.startdate and a.enddate
8 and to_char(da, 'd') in (1, 7)
9 group by a.startdate, a.enddate;
STARTDATE ENDDATE COUNT(DA)
----------- ----------- ----------
2008-11-11 2008-11-22 3
2008-12-11 2008-12-22 4