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