Re: Date Calculation

john bruin heeft geschreven in bericht <37239D2C.5444_at_tssc.co.nz>...
>Hi all
>
>I would like to know if it is possible to have a column showing the time
>difference between two time columns. It seems to work with a
>non-hours,min date format but otherwise not. Can anyone help here?
>
>ie Occurred Fixed Repair Time
> 4-feb-99 3:00 PM 10-feb-99 4:00 PM 6 DAYS 1 HOUR
>
>select
>to_char(date_occurred,'dd-mon-yy HH:MI PM') Occurred
>,to_char(date_fixed,'dd-mon-yy HH:MI PM') Fixed
>,something like - date_fixed - date_occurred 'Repair Time'
>
>Regards
>John

John,
It depends on what you really want to show. If you want the time difference in days, you can just substract the date_fields.
If you truncate the difference you get the number of elapsed days. If subtract this trunctation from the total difference, you get the fraction of the remaining day.
(like in: select (sysdate - trunc(sysdate)) from dual;) If you multiply this by 24 you get the number of hours elapsed on the remaining day (in decimals).
Multiply by 24 * 60, you get the minutes....etc.

Take care with subtracting time_fields. If you insert a time, oracle will fill in the date-part for you (taken from sysdate). This should get you started, I hope

Frans Hovenkamp Received on Mon Apr 26 1999 - 04:05:14 CDT