如:2010-08-25至2010-09-13 ,則共有14個工作日。
10 个解决方案
#1
自己写个函数
#2
这个需要自己写函数,除去双休日和法定日即可
#3
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 datetime from aa
where to_char(datetime,'day') not in ('星期六','星期日')
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 datetime from aa
where to_char(datetime,'day') not in ('星期六','星期日')
#4
create or replace function get_workday(startdate date,enddate date)
return number
as
v_num number(10);
v_days number(10);
begin
v_days:=trunc(enddate)-trunc(startdate)+1;
with t as (select decode(to_char(startdate+rownum-1,'d'),7,0,1,0,1) rn from all_objects where rownum<=v_days)
select sum(rn) into v_num from t;
return v_num;
end;
#5
你这个是得到从2010-08-25 到 2010-09-13的每一个工作日
不过是很经典的方法,虽然跑题了,但还是顶一下
#6
楼主要统计总共有多少个工作日,最后count(1)一下就可以了,呵呵
#7
/* ORACLE PL/SQL 計算兩個日期 間 工作日 數量 */
CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE )
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAY NUMBER :=0;
V_START_DATE DATE ;
BEGIN
V_START_DATE:=START_DATE ;
WHILE V_START_DATE <= END_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(DAY_COUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
END;
END ;
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'
#8
CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE )
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAY NUMBER :=0;
V_START_DATE DATE ;
BEGIN
IF START_DATE<=END_DATE THEN
V_START_DATE:=START_DATE ;
WHILE V_START_DATE <= END_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(DAY_COUNT);
ELSE
V_START_DATE:=END_DATE ;
WHILE V_START_DATE <= START_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(-DAY_COUNT);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
END;
END ;
/* ORACLE PL/SQL 計算兩個日期 間 工作日 數量 */
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
, apps.SGDF_WORK_DAY( TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD'),to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'
再次改進,當開始日期小於結束日期時,結果用負數表示 。
#9
--写个函数吧
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
#10
mark,这写法简洁,好。
#1
自己写个函数
#2
这个需要自己写函数,除去双休日和法定日即可
#3
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 datetime from aa
where to_char(datetime,'day') not in ('星期六','星期日')
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 datetime from aa
where to_char(datetime,'day') not in ('星期六','星期日')
#4
create or replace function get_workday(startdate date,enddate date)
return number
as
v_num number(10);
v_days number(10);
begin
v_days:=trunc(enddate)-trunc(startdate)+1;
with t as (select decode(to_char(startdate+rownum-1,'d'),7,0,1,0,1) rn from all_objects where rownum<=v_days)
select sum(rn) into v_num from t;
return v_num;
end;
#5
你这个是得到从2010-08-25 到 2010-09-13的每一个工作日
不过是很经典的方法,虽然跑题了,但还是顶一下
#6
楼主要统计总共有多少个工作日,最后count(1)一下就可以了,呵呵
#7
/* ORACLE PL/SQL 計算兩個日期 間 工作日 數量 */
CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE )
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAY NUMBER :=0;
V_START_DATE DATE ;
BEGIN
V_START_DATE:=START_DATE ;
WHILE V_START_DATE <= END_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(DAY_COUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
END;
END ;
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'
#8
CREATE OR REPLACE FUNCTION APPS.SGDF_WORK_DAY( START_DATE IN DATE ,END_DATE IN DATE )
RETURN NUMBER IS DAY_COUNT NUMBER :=0 ;
BEGIN
DECLARE V_DAY NUMBER :=0;
V_START_DATE DATE ;
BEGIN
IF START_DATE<=END_DATE THEN
V_START_DATE:=START_DATE ;
WHILE V_START_DATE <= END_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(DAY_COUNT);
ELSE
V_START_DATE:=END_DATE ;
WHILE V_START_DATE <= START_DATE LOOP
SELECT TO_CHAR(V_START_DATE,'D') INTO V_DAY FROM DUAL;
IF (V_DAY!='1') AND (V_DAY!='7') THEN
DAY_COUNT := DAY_COUNT +1 ;
END IF ;
V_START_DATE :=V_START_DATE + 1 ;
END LOOP;
RETURN(-DAY_COUNT);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
END;
END ;
/* ORACLE PL/SQL 計算兩個日期 間 工作日 數量 */
/*, ATTRIBUTE16,ATTRIBUTE13 */
select ORDER_NUMBER
,to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'),TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')
, apps.SGDF_WORK_DAY(to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY'), TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD')) AS DAYCOUNT
, apps.SGDF_WORK_DAY( TO_DATE(SUBSTR(ATTRIBUTE16,1,10),'YYYY-MM-DD'),to_date(SUBSTR(ATTRIBUTE13,1,11),'DD-MON-YYYY')) AS DAYCOUNT
from ont.oe_order_headers_all
where ORDER_NUMBER LIKE '6010017__'
再次改進,當開始日期小於結束日期時,結果用負數表示 。
#9
--写个函数吧
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
#10
mark,这写法简洁,好。