Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need to calc time between to dates in hours and minutes
Try:
TO_CHAR(TRUNC(SYSDATE)+(end_dt-start_dt),'hh24:mi')
This will function as long as the hours are less than 24. The TRUNC(SYSDATE) strips the time from the system date and allows the calculated time to be added in. IF the hours will exceed 24, this will truncate the hours at 24. To handele hours greater than 24, use: TO_CHAR(FLOOR((end_dt-start_dt)*24),'09') || ':'
|| TO_CHAR(TRUNC(SYSDATE)+(end_dt-start_dt),'mi')
HTH
James
In article <19990716232641.20172.00001661_at_ng-da1.aol.com>,
djordan532_at_aol.com (DJordan532) wrote:
> I need to calculate the difference between two dates and represent
them in a
> query in hours and minutes. Additionally, I need to aggregate them
into a
> column to use in calculating averages. Will be used in a PL/SQL
package. I
> have used this below in my query and it works but it is not accurate:
>
> start_dt = 16-JUL-99 15:10:00
> end_dt = 16-JUL-99 15:41:00
>
> select to_char((end_dt-start_dt)*(24*60),'990.99') elapsed from
tablex;
>
> returned valued is 30.90
>
> The value I would like to see is 0:31 for 0 hours and 31 minutes.
>
> I will appreciate all help.
>
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Jul 19 1999 - 09:23:07 CDT