오라클) 두날짜의 차이 구하기

 

CREATE OR REPLACE FUNCTION UF_BETWEEN_DATE
(IN_SDATE   IN DATE
,IN_EDATE   IN DATE
)
RETURN VARCHAR2
IS
    V_YEAR      NUMBER := 0;
    V_MON       NUMBER := 0;
    V_DAY       NUMBER := 0;
    V_HOUR      NUMBER := 0;
    V_MINUTE    NUMBER := 0;
    V_SECOND    NUMBER := 0;
   
    V_RETURN    VARCHAR2(50);
   
BEGIN

    IF IN_SDATE IS NULL THEN
        RETURN NULL;
    END IF;
   
    IF IN_EDATE IS NULL THEN
        RETURN NULL;
    END IF;

    SELECT TRUNC(MONTHS_BETWEEN(IN_EDATE, IN_SDATE)/12)         -- 년
         , TRUNC(MOD(MONTHS_BETWEEN(IN_EDATE, IN_SDATE),12))    -- 월
         , FLOOR(MOD(MONTHS_BETWEEN(IN_EDATE, IN_SDATE),1)*30.5)-- 일
         , TRUNC(MOD(IN_EDATE-IN_SDATE,1)*24)                   -- 시
         , TRUNC(MOD((IN_EDATE-IN_SDATE)*24,1)*60)              -- 분
         , TRUNC(ROUND(MOD((IN_EDATE-IN_SDATE)*24*60,1)*60))    -- 초
      INTO V_YEAR
         , V_MON
         , V_DAY
         , V_HOUR
         , V_MINUTE
         , V_SECOND
    FROM DUAL;

    V_RETURN := TRIM(TO_CHAR((V_YEAR * 365) + (V_MON * 30) + V_DAY)) || ' ' ||
                TO_CHAR(V_HOUR,'FM00') || ':' || TO_CHAR(V_MINUTE,'FM00') || ':' || TO_CHAR(V_SECOND,'FM00');
               

    RETURN V_RETURN;

EXCEPTION
    WHEN OTHERS THEN
        RETURN 'ERROR';
END;