You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




03 November 2006

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 d1 extract(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

2 Comments:

Blogger Nicolas Gasparotto said...

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,

6/11/06 16:22  
Blogger Laurent Schneider said...

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.

6/11/06 21:15  

Post a Comment

<< Home