case1:计算工期总天数
select to_date('20180105','yyyyMMdd')-to_date('20171123','yyyyMMdd') from dual;
--------------------------------------
case2:工期显示形式:几年几月几日
---计划工期 0307 (成品)
FUNCTION PLANWORKDATE(V_STARTTME VARCHAR2, V_ENDTIME VARCHAR2)
RETURN VARCHAR2;
--
---计划工期 0307
FUNCTION PLANWORKDATE(V_STARTTME VARCHAR2, V_ENDTIME VARCHAR2)
RETURN VARCHAR2 IS
WORKDAYS VARCHAR2(100);
SUNDAY NUMBER(30, 0);
A VARCHAR2(6) := SUBSTR(V_STARTTME, 1, 4);
B VARCHAR2(6) := SUBSTR(V_ENDTIME, 1, 4); --YEAR
C VARCHAR2(6) := SUBSTR(V_STARTTME, 5, 2);
D VARCHAR2(6) := SUBSTR(V_ENDTIME, 5, 2);--MONTH
E VARCHAR2(6) := SUBSTR(V_STARTTME, 7, 2);
F VARCHAR2(6) := SUBSTR(V_ENDTIME, 7, 2); --DAY
BYEAR VARCHAR2(30);
MDAYS VARCHAR2(200);
SDAY VARCHAR2(30);
EDAY VARCHAR2(30);
SUNDAYS VARCHAR2(30) := TO_DATE(V_ENDTIME, 'yyyyMMdd') -
TO_DATE(V_STARTTME, 'yyyyMMdd');
BEGIN
SELECT MONTHS_BETWEEN(TO_DATE(V_ENDTIME, 'yyyyMMdd'),
TO_DATE(V_STARTTME, 'yyyyMMdd'))
INTO MDAYS
FROM DUAL;
--MONTH
IF TRUNC(MDAYS) <= 0 THEN
MDAYS := '0';
ELSE
MDAYS := TRUNC(MDAYS);
END IF;
dbms_output.put_line(MDAYS);
----YEAR
IF TO_NUMBER(B) >= TO_NUMBER(A) THEN
IF MDAYS='0' THEN
BYEAR := TO_NUMBER(B) - TO_NUMBER(A);
END IF;
IF MDAYS >= 12 THEN
MDAYS := TRUNC(MDAYS) - 12;
FOR I IN REVERSE 1 .. 20 LOOP
IF MDAYS >= 12 THEN
MDAYS := MDAYS - 12;
ELSE
EXIT;
END IF;
END LOOP;
IF MDAYS<=TO_NUMBER(D) OR TO_NUMBER(E) <= TO_NUMBER(F) THEN
BYEAR := TO_NUMBER(B) - TO_NUMBER(A);
ELSE
BYEAR := TO_NUMBER(B) - TO_NUMBER(A)-1;
END IF;
END IF;
ELSE
WORKDAYS := '结束时间不可以大于开始时间';
END IF;
--DAY
IF TO_NUMBER(E) = TO_NUMBER(F) THEN
SUNDAY := '0';
ELSE
IF TO_NUMBER(E) < TO_NUMBER(F) THEN
SUNDAY := TO_NUMBER(F) - TO_NUMBER(E);
ELSE
IF C = '01' OR C = '03' OR C = '05' OR C = '07' OR C = '08' OR
C = '10' OR C = '12' THEN
SDAY := TO_NUMBER('31') - TO_NUMBER(E);
ELSE
SDAY := TO_NUMBER('30') - TO_NUMBER(E);
END IF;
EDAY := TO_NUMBER(F) - TO_NUMBER('01');
SUNDAY := TO_NUMBER(SDAY) + TO_NUMBER(EDAY);
END IF;
END IF;
------TURN_NUMBER
IF BYEAR >= 0 THEN
IF BYEAR>0 THEN
IF MDAYS <> 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' || TO_NUMBER(MDAYS) || '月' ||
TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS = 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' ||
TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS<>0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' || TO_NUMBER(MDAYS) || '月' ||
'(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS=0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' ||
'(' || SUNDAYS || '天' || ')';
END IF;
END IF;
ELSE
IF MDAYS <> 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(MDAYS) || '月' || TO_NUMBER(SUNDAY) || '天' || '(' ||
SUNDAYS || '天' || ')';
ELSE
IF MDAYS <> 0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(MDAYS) || '月' || '(' || SUNDAYS || '天' || ')';
ELSE
WORKDAYS := TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
END IF;
END IF;
RETURN WORKDAYS;
END;
select to_date('20180105','yyyyMMdd')-to_date('20171123','yyyyMMdd') from dual;
--------------------------------------
case2:工期显示形式:几年几月几日
---计划工期 0307 (成品)
FUNCTION PLANWORKDATE(V_STARTTME VARCHAR2, V_ENDTIME VARCHAR2)
RETURN VARCHAR2;
--
---计划工期 0307
FUNCTION PLANWORKDATE(V_STARTTME VARCHAR2, V_ENDTIME VARCHAR2)
RETURN VARCHAR2 IS
WORKDAYS VARCHAR2(100);
SUNDAY NUMBER(30, 0);
A VARCHAR2(6) := SUBSTR(V_STARTTME, 1, 4);
B VARCHAR2(6) := SUBSTR(V_ENDTIME, 1, 4); --YEAR
C VARCHAR2(6) := SUBSTR(V_STARTTME, 5, 2);
D VARCHAR2(6) := SUBSTR(V_ENDTIME, 5, 2);--MONTH
E VARCHAR2(6) := SUBSTR(V_STARTTME, 7, 2);
F VARCHAR2(6) := SUBSTR(V_ENDTIME, 7, 2); --DAY
BYEAR VARCHAR2(30);
MDAYS VARCHAR2(200);
SDAY VARCHAR2(30);
EDAY VARCHAR2(30);
SUNDAYS VARCHAR2(30) := TO_DATE(V_ENDTIME, 'yyyyMMdd') -
TO_DATE(V_STARTTME, 'yyyyMMdd');
BEGIN
SELECT MONTHS_BETWEEN(TO_DATE(V_ENDTIME, 'yyyyMMdd'),
TO_DATE(V_STARTTME, 'yyyyMMdd'))
INTO MDAYS
FROM DUAL;
--MONTH
IF TRUNC(MDAYS) <= 0 THEN
MDAYS := '0';
ELSE
MDAYS := TRUNC(MDAYS);
END IF;
dbms_output.put_line(MDAYS);
----YEAR
IF TO_NUMBER(B) >= TO_NUMBER(A) THEN
IF MDAYS='0' THEN
BYEAR := TO_NUMBER(B) - TO_NUMBER(A);
END IF;
IF MDAYS >= 12 THEN
MDAYS := TRUNC(MDAYS) - 12;
FOR I IN REVERSE 1 .. 20 LOOP
IF MDAYS >= 12 THEN
MDAYS := MDAYS - 12;
ELSE
EXIT;
END IF;
END LOOP;
IF MDAYS<=TO_NUMBER(D) OR TO_NUMBER(E) <= TO_NUMBER(F) THEN
BYEAR := TO_NUMBER(B) - TO_NUMBER(A);
ELSE
BYEAR := TO_NUMBER(B) - TO_NUMBER(A)-1;
END IF;
END IF;
ELSE
WORKDAYS := '结束时间不可以大于开始时间';
END IF;
--DAY
IF TO_NUMBER(E) = TO_NUMBER(F) THEN
SUNDAY := '0';
ELSE
IF TO_NUMBER(E) < TO_NUMBER(F) THEN
SUNDAY := TO_NUMBER(F) - TO_NUMBER(E);
ELSE
IF C = '01' OR C = '03' OR C = '05' OR C = '07' OR C = '08' OR
C = '10' OR C = '12' THEN
SDAY := TO_NUMBER('31') - TO_NUMBER(E);
ELSE
SDAY := TO_NUMBER('30') - TO_NUMBER(E);
END IF;
EDAY := TO_NUMBER(F) - TO_NUMBER('01');
SUNDAY := TO_NUMBER(SDAY) + TO_NUMBER(EDAY);
END IF;
END IF;
------TURN_NUMBER
IF BYEAR >= 0 THEN
IF BYEAR>0 THEN
IF MDAYS <> 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' || TO_NUMBER(MDAYS) || '月' ||
TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS = 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' ||
TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS<>0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' || TO_NUMBER(MDAYS) || '月' ||
'(' || SUNDAYS || '天' || ')';
END IF;
IF MDAYS=0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(BYEAR)|| '年' ||
'(' || SUNDAYS || '天' || ')';
END IF;
END IF;
ELSE
IF MDAYS <> 0 AND SUNDAY <> 0 THEN
WORKDAYS := TO_NUMBER(MDAYS) || '月' || TO_NUMBER(SUNDAY) || '天' || '(' ||
SUNDAYS || '天' || ')';
ELSE
IF MDAYS <> 0 AND SUNDAY = 0 THEN
WORKDAYS := TO_NUMBER(MDAYS) || '月' || '(' || SUNDAYS || '天' || ')';
ELSE
WORKDAYS := TO_NUMBER(SUNDAY) || '天' || '(' || SUNDAYS || '天' || ')';
END IF;
END IF;
END IF;
RETURN WORKDAYS;
END;