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′,
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
end “D2-D1″
from t
D1 D2 D2-D1
——————- ——————- ———-
01.02.2000 11:22:02 23.01.2000 12:00:03 -215:21:59

Published by

Laurent Schneider

Oracle Certified Master

2 thoughts on “difference between two dates”

  1. 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. 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.

Leave a Reply