create or replace function fun_workday(startdate in varchar2, enddate in varchar2)return number IS TOTALVALUE NUMBER; begin select count(*) into TOTALVALUE from ( select rownum rnum from all_objects where rownum <= to_date(enddate,'yyyy-MM-dd') - to_date(startdate,'yyyy-MM-dd')+1 ) where to_char( to_date(startdate,'yyyy-MM-dd')+rnum-1, 'D' ) not in ( '1', '7' ); return TOTALVALUE; end;--这个取出非周末周日的工作日。未考虑节假日调休神马的
周日通常为一周的第一天,从周日到周六对应的数字为1~7select * from tab where date>= start_date and date <=end_date and to_char(date,'d') not in ('1','7'); SELECT TO_CHAR(sysdate+3,'d')from DUAL; --今天星期三,结果为'4'
加入开始日期是2013-1-2,结束日期是2013-6-18,代码如下,你懂的select count(1) from ( select to_date('2013-01-02', 'yyyy-mm-dd') + level A from dual connect by to_date('2013-01-02', 'yyyy-mm-dd') + level < to_date('2013-06-18', 'yyyy-mm-dd') ) where to_char(a,'d') in(2,3,4,5,6)
create or replace function fun_workday(startdate in varchar2,
enddate in varchar2)return number
IS
TOTALVALUE NUMBER;
begin
select count(*) into TOTALVALUE
from ( select rownum rnum
from all_objects
where rownum <= to_date(enddate,'yyyy-MM-dd') -
to_date(startdate,'yyyy-MM-dd')+1 )
where to_char( to_date(startdate,'yyyy-MM-dd')+rnum-1, 'D' )
not in ( '1', '7' );
return TOTALVALUE;
end;--这个取出非周末周日的工作日。未考虑节假日调休神马的
where date>= start_date and date <=end_date
and to_char(date,'d') not in ('1','7');
SELECT TO_CHAR(sysdate+3,'d')from DUAL;
--今天星期三,结果为'4'
(
select to_date('2013-01-02', 'yyyy-mm-dd') + level A
from dual
connect by to_date('2013-01-02', 'yyyy-mm-dd') + level <
to_date('2013-06-18', 'yyyy-mm-dd')
)
where to_char(a,'d') in(2,3,4,5,6)