Infrastructure Software

SQL> select case when extract(month from sysdate) between 1 and 9 then add_months(trunc(sysdate,'year'),-3)
  2              else add_months(trunc(sysdate,'year'),9)
  3         end
  4         as dt
  5    from dual
  6  /

DT
---------
01-OCT-08

Hi,

Welcome to the forum!

Another approach:

ADD_MONTHS ( TRUNC ( ADD_MONTHS ( dt
                                , 3
                                )
                   , 'YYYY'
                   )
           , -3
           )

We can use "add_months" B-)

select column_value,
extract(year from add_months(column_value,-9)) as y,
add_months(trunc(add_months(column_value,-9),'yyyy'),9) as oct
from table(sys.odciDateList(
date '2009-01-01',date '2009-09-01',
date '2009-10-01',date '2009-12-01'));

COLUMN_V     Y  OCT
--------  ----  --------
09-01-01  2008  08-10-01
09-09-01  2008  08-10-01
09-10-01  2009  09-10-01
09-12-01  2009  09-10-01

If your fiscal year starts on 7/1, here is a way to provide the first day in a date field.

SELECT CASE
WHEN TO_CHAR(SYSDATE, 'MM') IN ('7','8','9','10','11','12')
then to_date(to_char(sysdate,'YYYY')||'0701','YYYYMMDD')
ELSE to_date (TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY') ) - 1 )||'0701','YYYYMMDD')
END
FROM DUAL;

another way,

SQL> SELECT ADD_MONTHS (TRUNC (SYSDATE, 'year')
  2                    ,DECODE (GREATEST (9, TO_CHAR (SYSDATE, 'MM')), 9, -3, 10)) dt
  3    FROM DUAL;
DT
---------
01-OCT-10
SQL> 

G.