date difference between two date datetype variable in HH:MM:SI in Oracle

Question

I am facing an issue where I have to take the difference between two date datetype variables in HH:MM:SS AM. For example if date1 stores 23-DEC-2014 02:00:00 PM and date2 stores 24-DEC-2014 02:00:00 PM then date2 - date1 should return 24:00:00.

I tried different to_char and likewise methods.

Can you please suggest what I should do to resolve this issue.

the shared link does not gives the answer that I am looking for — Sandeep, Dec 15 '14 at 05:50
Might be this http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql will help you — Exhausted, Dec 15 '14 at 06:26
`MM` is the Month, `MI` is the minutes; and `SI` is not a valid date format code. — Jeffrey Kemp, Dec 17 '14 at 06:08

Sylvain Leroux · Answer 1 · 2014-12-15T07:14:14.550

As you have plain DATE, the difference between two dates is expressed in fractional days. Some little arythmetics as explained in the related questions might help.

One other approach would be to cast the difference to an INTERVAL using NUMTODSINTERVAL. However, this does not work out-of-the-box, as (of 11g at least), the TO_CHAR function does not supports correctly INTERVAL.

However, as a workaround that is not provided in the related answers (or do I missed it?), you can cast to INTERVAL DAY TO SECOND using the right precision to achieve (more or less) what you are looking for:

Here is an example

with testdata as (select to_date('12/12/2014 09:00:00','DD/MM/YYYY HH:MI:SS') a,
                    to_date('10/11/2014 11:30:14','DD/MM/YYYY HH:MI:SS') b from dual)

select a-b "days",
       numtodsinterval(a-b, 'DAY') "ds interval",
       CAST(numtodsinterval(a-b, 'DAY') AS INTERVAL DAY(3) TO SECOND(0))
--     ^^^^^                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--         cast to 3 digit days interval -- no fractional seconds
from testdata

Producing (formatted as rows for display purpose):

days
31.8956712962962962962962962962962962963

ds interval
+000000031  21:29:46.000000000

CAST(NUMTODSINTERVAL(A-B,'DAY') AS INTERVAL DAY(3) TO SECOND(0))
+031 21:29:46

I don't know if/how you can get rid of the leading sign though

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Maybe this helps:

CREATE OR REPLACE FUNCTION get_date_diff(p_date1 IN DATE, 
                                         p_date2 IN DATE) RETURN VARCHAR2
IS
  v_seconds NUMBER;
  v_minutes NUMBER;
  v_hours   NUMBER;
  v_time    VARCHAR2(20);
BEGIN
  v_seconds := (p_date2 - p_date1) * 24 * 60 * 60;
  v_hours   := FLOOR(v_seconds / 3600);
  v_minutes := FLOOR((v_seconds - (v_hours * 3600)) / 60);
  v_seconds := FLOOR(v_seconds - (v_hours * 3600) - (v_minutes * 60));
  
  v_time    := CASE WHEN v_hours < 100 
                    THEN LPAD(TO_CHAR(v_hours), 2, '0')
                    ELSE TO_CHAR(v_hours)
                END || ':' ||
               LPAD(TO_CHAR(v_minutes), 2, '0') || ':' ||
               LPAD(TO_CHAR(v_seconds), 2, '0');
  
  RETURN v_time;
END;
/

SAMPLE INPUT

p_date1:=to_date('20/11/2014 11:30:45','DD/MM/YYYY HH:MI:SS')

p_date2 :=to_date('15/12/2014 09:00:00','DD/MM/YYYY HH:MI:SS')

SAMPLE OUTPUT

597:29:15

Thanks everyone for the reply I will check the posts shared by you and will keep you posted. — Sandeep, Dec 15 '14 at 07:14

date difference between two date datetype variable in HH:MM:SI in Oracle

2 Answers2