Re: date field subtraction problem

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/02/05
Message-ID: <5d8ikq$2od@shadow.CSUFresno.EDU>#1/1

In article <32F0852F.7A93_at_draeger.com>,
Frank Launer <Frank.Launer_at_draeger.com> wrote:
>Hi,
>
>I've a problem with subtracting two columns.
>
>For the subtraction I want to write a database-trigger.
>
> create table test(prod_date DATE, // YY-MON-YY
> vert_date NUMBER(6) // YYYYWW
> prod_ver_diff NUMBER(4));
>
>e.g. prod_date = '12-JAN-97'
>e.g. vert_date = 198050
>
>I want to have the number of weeks between the two "dates"
> ( prod_date - vert_date = number of weeks ).
>
>I tried several algorithms without success.

Here's a little script I put together. It comes close to the correct number of weeks, but may be incorrect due to 7-day rounding for a week.

Regards,
Steve Cosner

---

drop table tmp;
create table tmp (d1 date,vd number(6),diff number(4)); insert into tmp values(to_date('03011997','MMDDYYYY'),199702,null); select * from tmp;
declare
  d1 date;
  vd number(6);

  vd_yr char(4);
  vd_wk   integer;
  vd_days integer;
  vd_date date;

  diffc integer;
  cursor c is select d1,vd from tmp;
begin

    open c; fetch c into d1,vd; close c;

    vd_yr   := substr(to_char(vd),1,4);
    vd_wk   := to_number(substr(to_char(vd),5,2));
    vd_days := 7 * vd_wk;
    vd_date := to_date(vd_yr||to_char(vd_days,'999'),'YYYYDDD');

    diffc := (d1 - vd_date) / 7;
    update tmp set diff=diffc;
end;
.
/
select * from tmp;
drop table tmp; Received on Wed Feb 05 1997 - 00:00:00 CST