Wednesday, October 22, 2008

ORACLE : Minutes between Two dates

ORACLE : Minutes between Two dates
The following function is returning the No. of minutes between two specific dates.

 FUNCTION F_GET_MINUTES_DIFF
 (
   dSTARTDATE    DATE,
   dENDDATE    DATE
 ) RETURN NUMBER
 IS
   v_MINSDIFF   NUMBER(5);
 BEGIN
   SELECT
    ROUND(
     (((TRUNC(dSTARTDATE) + 1) - dSTARTDATE) * 1440)
     + ((dENDDATE - TRUNC(dENDDATE)) * 1440)
     + (((TRUNC(dENDDATE) - TRUNC(dSTARTDATE))-1) * 1440),
    2) INTO v_MINSDIFF
   FROM
    DUAL;
   RETURN v_MINSDIFF;
 END F_GET_MINUTES_DIFF;

Example to call the function

 DECLARE
   MINSDIFF NUMBER(5);
 BEGIN
   MINSDIFF := F_GET_MINUTES_DIFF(TO_DATE('21-10-2008 12:00:01 AM', 'dd-mm-yyyy hh:mi:ss pm'), SYSDATE);
   DBMS_OUTPUT.PUT_LINE(MINSDIFF);
 END;

No comments: