CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC (i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
/
select to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss') from dual;
6.0939236111111111111111111111111111111
select to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from dual;
6 days 02:15:15