Time Function: calculate total Time (HH:MI:SS format)

 

CREATE OR REPLACE FUNCTION F_WORKING_TIME

   (START_TIME    VARCHAR2,   END_TIME   VARCHAR2)

   RETURN   VARCHAR2  IS

   V_TIME_IN                 NUMBER(10);

   V_TIME_OUT                NUMBER(10);

   VHH                       VARCHAR2(8);

   VMM                       VARCHAR2(8);

   VSS                       VARCHAR2(8);

   V_TOT_WORKED              NUMBER(10);

   V_WORKED                  VARCHAR2(8);

BEGIN

   V_TIME_IN :=TO_NUMBER(SUBSTR(START_TIME,1,2))*60*60+TO_NUMBER(SUBSTR(START_TIME,4,2))*60+TO_NUMBER(SUBSTR(START_TIME,7,2));

   V_TIME_OUT:=TO_NUMBER(SUBSTR(END_TIME,1,2))*60*60+TO_NUMBER(SUBSTR(END_TIME,4,2))*60+TO_NUMBER(SUBSTR(END_TIME,7,2));

   V_TOT_WORKED:=V_TIME_OUT-V_TIME_IN;

   VHH:=TRUNC(V_TOT_WORKED/(60*60));

   VMM:=TRUNC((V_TOT_WORKED-VHH*60*60)/60);

   VSS:=TRUNC(V_TOT_WORKED-((VHH*60*60)+VMM*60));

   V_WORKED:=LPAD(VHH,2,'0')||':'||LPAD(VMM,2,'0')||':'||LPAD(VSS,2,'0');

   RETURN V_WORKED;

END;

/

SELECT F_WORKING_TIME('12:00:00','16:35:00') FROM DUAL;