Re: PL/SQL

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: 2000/03/07
Message-ID: <38d27320.20243859@netnews.worldnet.att.net>#1/1

Use TRUNC to truncate the date to Jan 1. Then use ADD_MONTHS to subtract the required number of months. The following example subtracts 120 months, or 10 years:

  1* select add_months(trunc(sysdate,'year'),-120) from dual SQL> / ADD_MONTH

---

01-JAN-90 There may be other approaches, but this should work.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Mon, 06 Mar 2000 20:20:05 GMT, "Buck Turgidson" <jcmanNOSPAM_at_worldnet.att.net> wrote:

>I am trying to write a PL/SQL to generate year-begin dates for the past 10
>years, based on sysdate. It is not working properly. I don't write a lot
>of PL/SQL. So any help would be appreciated
>
>
>
>DECLARE
> YEARS INTEGER := 10;
> PRIOR_YEAR DATE := SYSDATE;
> CURSOR PS_CURSOR IS
> SELECT
> TRUNC(TRUNC(TO_DATE(PRIOR_YEAR),'YEAR') - 1,'YEAR') AS "YR_BEGIN"
> FROM DUAL;
> PS_REC PS_CURSOR%ROWTYPE;
>BEGIN
> OPEN PS_CURSOR;
>LOOP
> FETCH PS_CURSOR INTO PS_REC;
> EXIT WHEN YEARS = 0;
> YEARS := YEARS - 1;
> PRIOR_YEAR := PS_REC.YR_BEGIN;
> DBMS_OUTPUT.put_line(PRIOR_YEAR);
> PRIOR_YEAR := PRIOR_YEAR - 1;
>END LOOP;
> CLOSE PS_CURSOR;
>END;
>
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>01-JAN-1999
>
>PL/SQL procedure successfully completed.
>
>
>
Received on Tue Mar 07 2000 - 00:00:00 CST