执行环境：ORACLE SQL

不考虑节日。

Dear Jungle ,

煩請增加下面自定義FUNCTION，并且分配用戶權限。

業務部要求計算兩個日期間的工作日（即周一至周五）數量。

Function 代碼如下：
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 '6010016__'

ITPub博客

計算兩個日期間的工作日數量--Function

最新文章