difference between two dates

How can i get the difference in days between two dates, d1 and d2 ?

for example :

with t as (select
  to_date('2000-02-01','YYYY-MM-DD') d1,
  to_date('2000-01-23','YYYY-MM-DD') d2
  from dual)
select d1,d2,d2-d1
from t;
D1         D2              D2-D1
---------- ---------- ----------
01.02.2000 23.01.2000         -9

How can i get the difference in hours:minutes:seconds between two dates, d1 and d2 ?
Use the interval datatype. To have a format different than the default +000000000 00:00:00.000000, use extract


with t as (select
to_date(‘2000-02-01 11:22:02’,
‘YYYY-MM-DD HH24:MI:SS’) d1,
to_date(‘2000-01-23 12:00:03’,
‘YYYY-MM-DD HH24:MI:SS’) d2
from dual)
select d1,d2,case
when d1d2 then
‘-‘||
(extract(day from ((d1 – d2)
day to second))*24 +
extract(hour from ((d1 – d2)
day to second)))||’:’||
extract(minute from ((d1 – d2)
day to second))||’:’||
extract(second from ((d1 – d2)
day to second))
when d1=d2 then
‘0:0:0’
end “D2-D1”
from t
/
D1 D2 D2-D1
——————- ——————- ———-
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59

2 thoughts on “difference between two dates

  1. Nicolas Gasparotto

    Hi, Laurent
    Extract is nice, I have still an old fashion here, what about that ?
    SQL> with t as (select
    2 to_date(‘2000-02-01 11:22:02’,
    3 ‘YYYY-MM-DD HH24:MI:SS’) d1,
    4 to_date(‘2000-01-23 12:00:03’,
    5 ‘YYYY-MM-DD HH24:MI:SS’) d2
    6 from dual)
    7 select d1,d2,
    8 case when d1 > d2 then trunc((d1-d2)*-24)
    9 ||’:’
    10 ||to_char(trunc(sysdate)+(d1-d2),’MI:SS’)
    11 when d1

    Best regards,

  2. Laurent Schneider

    yes, this is the good old style. I wanted to show using extract with intervals (not date), because it is truely an interval (not a date), as documented in the sql reference

    Interval datatypes do not have format models. Therefore, to adjust their presentation, you must combine character functions such as EXTRACT and concatenate the components.

Comments are closed.