SQL> edi 已写入 file afiedt.buf 1 create or replace function fun_days(v_start date,v_end date) return number 2 as 3 num number; 4 total number:=0; 5 dt1 date; 6 begin 7 dt1:=trunc(v_start); 8 num:=abs(trunc(v_start)-trunc(v_end)); 9 for i in 0..num loop 10 if (to_char(dt1,'d')<>1 and to_char(dt1,'d')<>7) then 11 total:=total+1; 12 end if; 13 dt1:=dt1+1; 14 end loop; 15 return total; 16 exception 17 when others then 18 return 0; 19* end; SQL> /函数已创建。 SQL> edi 已写入 file afiedt.buf 1 select fun_days(TO_DATE('2010-08-25','YYYY-MM-DD'),TO_DATE('2010-09-13','YYYY-MM-DD')) days 2* from dual SQL> / DAYS ---------- 14
create table aa (datetime date);insert into aa select to_date('2010-08-25','YYYY-MM_DD')+rownum-1 as datetime from dual connect by rownum<=(to_date('2010-09-13','YYYY-MM_DD')-to_date('2010-08-25','YYYY-MM_DD'))+1;select count(1) from aa where to_char(datetime,'day') not in ('星期六','星期日');
借楼上大师的一用 呵呵with aa as (select to_date('2010-08-25','YYYY-MM_DD')+rownum-1 as datetime from dual connect by rownum<=(to_date('2010-09-13','YYYY-MM_DD')-to_date('2010-08-25','YYYY-MM_DD'))+1) select count(1) from aa where to_char(datetime,'day') not in ('星期六','星期日');
SQL> edi
已写入 file afiedt.buf 1 create or replace function fun_days(v_start date,v_end date) return number
2 as
3 num number;
4 total number:=0;
5 dt1 date;
6 begin
7 dt1:=trunc(v_start);
8 num:=abs(trunc(v_start)-trunc(v_end));
9 for i in 0..num loop
10 if (to_char(dt1,'d')<>1 and to_char(dt1,'d')<>7) then
11 total:=total+1;
12 end if;
13 dt1:=dt1+1;
14 end loop;
15 return total;
16 exception
17 when others then
18 return 0;
19* end;
SQL> /函数已创建。
SQL> edi
已写入 file afiedt.buf 1 select fun_days(TO_DATE('2010-08-25','YYYY-MM-DD'),TO_DATE('2010-09-13','YYYY-MM-DD')) days
2* from dual
SQL> / DAYS
----------
14
connect by rownum<=(to_date('2010-09-13','YYYY-MM_DD')-to_date('2010-08-25','YYYY-MM_DD'))+1;select count(1) from aa
where to_char(datetime,'day') not in ('星期六','星期日');
connect by rownum<=(to_date('2010-09-13','YYYY-MM_DD')-to_date('2010-08-25','YYYY-MM_DD'))+1)
select count(1) from aa
where to_char(datetime,'day') not in ('星期六','星期日');