Re: date field subtraction problem

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/04
Message-ID: <32f696cb.1850020@nntp.mediasoft.net>#1/1

On Thu, 30 Jan 1997 12:30:15 +0100, 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
>

Try:

declare

prod_date date := to_date( '12-JAN-97' ); vert_date number := 198050;
result number;
begin

    select ( to_number(substr( vert_date, 1, 4 )) * 52 +
             to_number(substr( vert_date, 5, 2)) ) -
           ( to_number(to_char( prod_date, 'YYYY')) * 52 +
             to_number(to_char( prod_date, 'IW')) )
      into result
      from dual;

dbms_output.put_line( ' Weeks Between is ' || result ); end;
/

>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.
>
>Who can help me?
>
>Database-Version Release 7.0.16.6.0
>
>-----------------------------------------------------------
>Frank Launer
>
>Draeger Synematic GmbH

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com

http://govt.us.oracle.com

Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Feb 04 1997 - 00:00:00 CST