Oracle : how to subtract two dates and get minutes of the result

16

I wrote this function to get minutes from a date, but I cannot get minutes between two dates, How to get that ?

FUNCTION get_minute(p_date DATE)
RETURN NUMBER
IS
BEGIN
    IF p_date IS NOT NULL THEN
        return  EXTRACT(MINUTE FROM TO_TIMESTAMP(to_char(p_date,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH24:MI:SS'));
    ELSE
        RETURN 0;
    END IF;
END get_minute;

This question is tagged with oracle date plsql

~ Asked on 2012-11-16 00:49:18

The Best Answer is


46

When you subtract two dates in Oracle, you get the number of days between the two values. So you just have to multiply to get the result in minutes instead:

SELECT (date2 - date1) * 24 * 60 AS minutesBetween
FROM ...

~ Answered on 2012-11-16 01:05:33


11

For those who want to substrat two timestamps (instead of dates), there is a similar solution:

SELECT ( CAST( date2 AS DATE ) - CAST( date1 AS DATE ) ) * 1440 AS minutesInBetween
FROM ...

or

SELECT ( CAST( date2 AS DATE ) - CAST( date1 AS DATE ) ) * 86400 AS secondsInBetween
FROM ...

~ Answered on 2017-06-16 10:46:15


Most Viewed Questions: