> 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.
>
>
>

Answer 1:

try followings

declare
v_year date := sysdate;
begin
for i in 1..10 loop
dbms_output.put_line(to_char(trunc(v_year,'YYYY'),'YYYY-MM-DD'));
v_year := add_months(v_year, -12);
end loop;
end;
/
 

Answer 2:

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