Difference between two dates in oracle

Question

How to find difference between two dates in oracle?

I tried with Extract method but it is not working properly.

Format should be hh:mm:ss

Answer 1

EXTRACT works on datetime or interval value expression. When you subtract two dates, you get a number which is the number fo days. If you want to use extract you should convert it into interval datatype using NUMTODSINTERVAL and then use EXTRACT.

SELECT   TO_DATE ('14-01-2014 14:00:00', 'dd-mm-yyyy hh24:mi:ss')
       - TO_DATE ('13-01-2014 22:30:45', 'dd-mm-yyyy hh24:mi:ss')
  FROM DUAL;

0.6453125

SELECT NUMTODSINTERVAL (
            TO_DATE ('14-01-2014 14:00:00', 'dd-mm-yyyy hh24:mi:ss')
          - TO_DATE ('13-01-2014 22:30:45', 'dd-mm-yyyy hh24:mi:ss'),
          'DAY')
  FROM DUAL;

+00 15:29:15.000000

SELECT    EXTRACT (HOUR FROM intrvl)
       || ':'
       || EXTRACT (MINUTE FROM intrvl)
       || ':'
       || EXTRACT (SECOND FROM intrvl)
  FROM (SELECT NUMTODSINTERVAL (
                    TO_DATE ('14-01-2014 14:00:00', 'dd-mm-yyyy hh24:mi:ss')
                  - TO_DATE ('13-01-2014 22:30:45', 'dd-mm-yyyy hh24:mi:ss'),
                  'DAY')
                  AS intrvl
          FROM DUAL);

15:29:15

You can even cast the date as timestamp, so that the difference is already in interval datatype.

SELECT    EXTRACT (HOUR FROM intrvl)
       || ':'
       || EXTRACT (MINUTE FROM intrvl)
       || ':'
       || EXTRACT (SECOND FROM intrvl)
  FROM (SELECT   CAST (
                    TO_DATE ('14-01-2014 14:00:00', 'dd-mm-yyyy hh24:mi:ss') AS TIMESTAMP)
               - CAST (
                    TO_DATE ('13-01-2014 22:30:45', 'dd-mm-yyyy hh24:mi:ss') AS TIMESTAMP)
                  AS intrvl
          FROM DUAL);