Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Determine the first day of the current Fiscal Year

684894Feb 13 2009 — edited Mar 16 2011
I am a SQL Server developer who is trying to to learn Oracle SQL. I am trying to write a query that will 1) determine the month number and if the number is 10, 11, or 12, will return '01-Oct-' of the current Calendar year. If the month number is between 1 and 9, it returns '01- Oct-' of the last Calendar year (YYYY = current Calendar year minus 1 year).

I was playing with the EXTRACT function to get the year and month, but was unable to formulate the '1-Oct-YYYY' where YYYY is the current or previous calendar year, depending on whether the SYSDATE falls before or after 1-October.

Could anyone point me to an example of how to do this in Oracle?

Comments

Karthick2003 
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
Frank Kulash
Hi,

Welcome to the forum!

Another approach: 
ADD_MONTHS ( TRUNC ( ADD_MONTHS ( dt
                                , 3
                                )
                   , 'YYYY'
                   )
           , -3
           )
Aketi Jyuuzou
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
838267
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;
Ganesh Srivatsav
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.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 13 2011
Added on Feb 13 2009
5 comments
16,711 views