# Thread: How Calculate time between 2 date .

1. Registered User
Join Date
Oct 2003
Posts
28

## Unanswered: How Calculate time between 2 date .

For exemple we just want have this result with good format 'HH:MIS'

select to_char(DAT_FIN_TRT,'HH:MIS') as "Heure_Fin_TRT" ,
to_char(DAT_DEBUT_TRT,'HH:MIS') as "Heure_DEB_TRT" ,
(To_char(DAT_FIN_TRT,'HHMISS') - to_char(DAT_DEBUT_TRT,'HHMISS') ) as durée
from coeadwh.tp_controle_batch where rownum < 15;

results :

Star_JOB End_JOB LAST

03:01:35 02:33:26 6809
03:12:16 02:33:32 7884
03:04:31 02:33:44 7087
03:11:07 02:33:28 7779
03:03:01 02:33:26 6975
01:16:29 01:14:41 188
01:31:50 01:14:56 1694
01:19:42 01:15:03 439
01:19:41 01:15:11 430
01:17:33 01:17:03 30
01:18:35 01:15:43 292

We juste want for exemple for the first row : Last = 28 minutes and 09 seconds !!!
and so on for the all the others lines ...

Is somebody has got an idea ...

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Provided Answers: 1
This should do it:
Code:
```SELECT start_job
,      end_job
,      to_char(floor(days*24)) || ':'
|| ltrim(to_char(floor(days*24*60) - floor(days*24)*60,'00')) || ':'
|| ltrim(to_char(floor(days*24*60*60) - floor(days*24*60)*60,'00')) as time
from (select start_job, end_job, end_job-start_job days from mytable);```
You could make that complex expression for "time" into a function so that your query becomes:
Code:
```select start_job
,      end_job
,      format_time(end_job-start_job) time from mytable;```

3. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
You can also try something like:

Code:
```
select start_job,
end_job,
floor(interval*24) hours,
floor(interval*24*60) minutes,
mod(interval*24*60*60,60) secs
from (select DAT_FIN_TRT end_job,
DAT_DEBUT_TRT start_job,
DAT_FIN_TRT - DAT_DEBUT_TRT interval
from coeadwh.tp_controle_batch where rownum < 15)
```

4. Registered User
Join Date
Dec 2003
Posts
148
You could also just make a function like:
Full article from ask tom: http://asktom.oracle.com/~tkyte/Misc/DateDiff.html
Code:
```FUNCTION        "DATEDIFF" ( p_what in varchar2,
p_d1   in date,
p_d2   in date ) return number
as
l_result    number;
begin
select (p_d2-p_d1) *
decode( upper(p_what),
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
into l_result from dual;

return l_result;
end;

13 rows selected.

SQL>```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•