title: Oracle Duration Function
body: <p>Why isn't my PL/SQL duration function working correctly?  In the query below, I manually calculate the 'hh:mm' the same way as in the function below.  However, I get different results.</p>
  
  <p>Calling Query:</p>
  
  <pre><code>WITH durdays AS (SELECT SYSDATE - (SYSDATE - (95 / 1440)) AS durdays FROM DUAL)
  SELECT TRUNC (24 * durdays) AS durhrs,
         MOD (TRUNC (durdays * 1440), 60) AS durmin,
         steven.duration (SYSDATE - (95 / 1400), SYSDATE) duration
    FROM durdays
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>durhrs:   1
  durmin:   35
  duration: '1:38'
  </code></pre>
  
  <p>Function code:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION steven.duration (d1 IN DATE, d2 IN DATE)
     RETURN VARCHAR2 IS
     tmpvar   VARCHAR2 (30);
     durdays   NUMBER (20,10); -- Days between two DATEs
     durhrs   BINARY_INTEGER; -- Completed hours
     durmin   BINARY_INTEGER; -- Completed minutes
  BEGIN
     durdays   := d2-d1; 
     durhrs   := TRUNC(24 * durdays);   
     durmin   := MOD (TRUNC(durdays * 1440), 60);
     tmpvar   := durhrs || ':' || durmin;
     RETURN tmpvar;
  END duration;
  /
  </code></pre>