Find out hours and minutes between two Date

Find Out Hours and Minutes Between Two Dates

Topic Introduction: Sometimes we need to find out the Hours and Minutes between two days. Here Day, Month, and Year will not be shown only in hours and minutes. Today we will see how to find out hours and minutes between two dates using SQL Query and PL/SQL Function.

SQL Query for Getting Hours & Minutes

 WITH
    DT
    AS
        (
        SELECT TO_DATE(TO_CHAR(:EDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')- TO_DATE(TO_CHAR(:SDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS') INTER FROM DUAL
        )
    --SELECT TRUNC (24 * INTER) ||' Hour '  || ROUND((24 * INTER - TRUNC (24 * INTER)) * 60)||' Minute' HOUR_MIN
    SELECT TRUNC (24 * INTER) HOURS, ROUND((24 * INTER - TRUNC (24 * INTER)) * 60) MINS

    FROM DT;

PL/SQL Function For Getting Hours And Minutes

CREATE OR REPLACE FUNCTION GET_HOURMIN( P_SDATE DATE,P_EDATE DATE) 
RETURN VARCHAR2
IS
    V_HOURMIN VARCHAR2(100);
BEGIN
    
 WITH
    DT
    AS
        (
        SELECT TO_DATE(TO_CHAR(P_EDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')- TO_DATE(TO_CHAR(P_SDATE, 'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS') INTER FROM DUAL
        )
    SELECT TRUNC (24 * INTER) ||' Hour '  || ROUND((24 * INTER - TRUNC (24 * INTER)) * 60)||' Minute' HOUR_MIN
    --SELECT TRUNC (24 * INTER) HOURS, ROUND((24 * INTER - TRUNC (24 * INTER)) * 60) MINS
INTO V_HOURMIN
    FROM DT;
    RETURN V_HOURMIN;
EXCEPTION 
WHEN NO_DATA_FOUND THEN RETURN NULL;
WHEN OTHERS THEN RETURN NULL;
END;

/