Re: forms 3.0/difference bet dates

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/03/14
Message-ID: <5gc0ic$hdc_at_info.csufresno.edu>#1/1

In article <1997Mar13.120636_at_admin1.tcd.ie>, <mcgrathm_at_admin1.tcd.ie> wrote:
> A form has to automatically calculate the number of years and months
> between two dates e.g. between 13-MAR-1997 AND 13-MAR-1998 would
> be represnted as 01.00 (YY.MM). The difference between 13-mar-97
> and 13-apr-1997 would be 00.01.
>
> I've been unable to develop a script/procedure to solve this
> problem and wonder has anyone else encountered a simliar problem?

No, I haven't encountered a similar problem, but thought it might be fun to try...

You need to divide the month number (minus 1) by 12 to get a decimal fraction to add to the year. Then subtract the first year/month number from the second. Finally, convert the remainder fraction back to months by multiplying by 12.

Here is some code:
  Function DDiff (D1 in date,D2 in date) Return varchar2 is     NumDiff Number(5,3);
    NumYr Integer;

    Function Df(D in Date) Return Number is begin

      Return(To_Number(To_Char(D,'YYYY'))
             + Round((To_Number(To_Char(D,'MM'))-1)/12,3));

    End;
  Begin
    NumDiff:= Df(D2)-Df(D1);
    NumYr := Trunc(NumDiff,0);
    Return(Ltrim(To_Char(NumYr,'09'))

              ||'.'||Ltrim(To_Char(12*(NumDiff-NumYr),'09')));   End;

Create the above function in your form just like you would a procedure, then when you need to use it, do this:

  :block.between_dates := DDiff(:block.first_date,:block.Second_Date);

Have fun!
Steve Cosner Received on Fri Mar 14 1997 - 00:00:00 CET