Re: Date Calculation

OK,

Here is something I cooked up to get the information Frans is looking for. The sql compares a date from a table with sysdate, and generates the interval between the datestamps.

--This select returns the interval in Years, Months, Days, Hours, and Minutes...

Select to_char(sysdate, 'yyyy-Mon-dd hh24:mi:ss') End_date,

        to_char(effdate, 'yyyy-Mon-dd hh24:mi:ss')  Start_date,
         trunc(months_between(sysdate, effdate)/12)  years,
         trunc(months_between(sysdate, effdate)
            - 12*trunc(months_between(sysdate, effdate)/12)) months,
         trunc(sysdate - add_months(effdate, trunc(months_between(sysdate,
effdate)))) days,
         trunc(24 * (sysdate - effdate - trunc(sysdate-effdate))) hours,
         60 * (24*(sysdate - effdate - trunc(sysdate-effdate))
            - trunc(24 * (sysdate - effdate - trunc(sysdate-effdate)))) minutes
       from my.table

• This select returns the interval in Days, Hours, and Minutes....

Select to_char(sysdate, 'yyyy-Mon-dd hh24:mi:ss') End_date,

        to_char(effdate, 'yyyy-Mon-dd hh24:mi:ss')  Start_date,
         trunc(sysdate - effdate) days,
         trunc(24 * (sysdate - effdate - trunc(sysdate-effdate))) hours,
         60 * (24*(sysdate - effdate - trunc(sysdate-effdate))
            - trunc(24 * (sysdate - effdate - trunc(sysdate-effdate)))) minutes
       from my.table

I have made no attempt to deal with the situation where end_date is before start_date (the results of the queries in that circumstance are pretty useless).

These queries look like we are working too hard to get the desired results. If someone can suggest "simplified" versions, please do.

Patrick Suppes

Frans Hovenkamp wrote:

> 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 - 18:09:21 CDT