How can I calculate the time bewtween two dates??

[hendoo49]

Using PL/SQL in Oracle I would like to find out the time between two dates. i.e. Given 20-July-1999 10:00 to 22-July-1999 13:00 the time between would be 2 days and 3 hours. The way I am doing it I get a decimal returned. For the example above I would get something back like 2.12467893. This is 2 full days and .12467893 of a day. I am trying to figure out how long an issue has been open and this format is not easily read. Any help would be appreciated. <br>
<br>
Thanks in advance

 

[kalyan]

May be this will help, though it is a long sql statement.<br>
<br>
select trunc(date2 - date1) Days,<br>
trunc(((date2 - date1) - trunc(date2 - date1)) * 24) Hours,<br>
trunc(((((date2 - date1) - trunc(date2 - date1)) * 24)<br>
- trunc(((date2 - date1) - trunc(date2 - date1)) * 24)) * 60) Minutes,<br>
trunc((((((date2 - date1) - trunc(date2 - date1)) * 24)<br>
- trunc(((date2 - date1) - trunc(date2 - date1)) * 24)) * 60) - trunc(((((date2 - date1) - trunc(date2 - date1)) * 24)<br>
- trunc(((date2 - date1) - trunc(date2 - date1)) * 24)) * 60))* 60 Seconds<br>
from dual;<br>
<br>
Check for open and close brackets. This works.<br>
<br>
Cheers<br>
Kalyan

 

[hendoo49]

Thanks Kalyan. Since time was an issue I already found a work around. We converted the end result (2.124678) into a day and hour format. Not the best answer but it worked. I think in the future I may want to use your solution and since it is a long statement I may create it as a function and just call the function out when it's needed. Thanks for your reply. It will help.