create or replace function p_calc_work_hrs (p_min_Date in date, p_max_Date in date) return varchar2 is v_max_hrs varchar2(2); v_max_mint varchar2(2); v_min_hrs varchar2(2); v_min_mint varchar2(2); v_hrs_work varchar2(2); v_min_work varchar2(2); begin v_max_hrs := SUBSTR(to_char(p_max_date,'DD/MM/RRRR HH24:MI'),12,2); v_max_mint := SUBSTR(to_char(p_max_date,'DD/MM/RRRR HH24:MI'),15,2); v_min_hrs := SUBSTR(to_char(p_min_date,'DD/MM/RRRR HH24:MI'),12,2); v_min_mint := SUBSTR(to_char(p_min_date,'DD/MM/RRRR HH24:MI'),15,2); if trunc(p_min_date) <> trunc(p_max_Date) then v_max_hrs := ltrim(rtrim(to_char(to_number(v_max_hrs) + 24))); end if; DBMS_OUTPUT.PUT_LINE(v_max_hrs||'==='||to_char(p_min_date,'DD/MM/RRRR')||'==='||to_char(p_max_date,'DD/MM/RRRR')); if v_min_mint > v_max_mint then v_hrs_work := lpad(ltrim(rtrim(to_char(to_number(v_max_hrs)-to_number(v_min_hrs)-1))),2,'0'); v_min_work := lpad( to_char((60-v_min_mint)+v_max_mint),2,'0'); else v_hrs_work := lpad(ltrim(rtrim(to_char(to_number(v_max_hrs)-to_number(v_min_hrs)))),2,'0'); v_min_work := lpad(ltrim(rtrim(to_char(abs(to_number(v_max_mint)-to_number(v_min_mint))))),2,'0'); end if; return v_hrs_work||':'||v_min_work; end;