difference between two dates
How can i get the difference in days between two dates, d1 and d2 ?
-
for example :
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
-
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 d1extract(day from ((d2 - d1)
day to second))*24 +
extract(hour from ((d2 - d1)
day to second))||':'||
extract(minute from ((d2 - d1)
day to second))||':'||
extract(second from ((d2 - d1)
day to second))
when d1>d2 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
Posted by Laurent Schneider at 11/03/2006 02:29:00 PM
Permalink : difference between two dates
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
2 Comments:
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 < d2 then trunc((d2-d1)*24)
12 ||':'
13 ||to_char(trunc(sysdate)+(d2-d1),'MI:SS')
14 else '0:00:00' end as Diff
15 from t
16 /
D1 D2 DIFF
------------------- ------------------- ----------
01/02/2000 11:22:02 23/01/2000 12:00:03 -215:21:59
SQL>
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.
Post a Comment
<< Home