5 Replies Latest reply: Mar 20, 2013 3:35 PM by 285499

# How to calculate Fiscal year based on date

HI all,

I have a sql statement which simply queries a single table. The table contains an 'effective date' i.e. to_date('01/09/2010', 'dd/mm/yyyy').

I am creating a view based on this query and want to append a psedo column with the dates fiscal year! the fiscal year we are using is the 01/08/.... tot he 31/07/....

I have been trying a few things and found this example on the net which i have been pkaying with, but am completelty clueless as to where the 83days comes in??

select 'FY'||trunc(trunc(to_date('01/09/2010', 'dd/mm/yyyy') + interval '83' day), 'Y') from dual;

I would be really interested to see how you think this can be done.

Thanks
• ###### 1. Re: How to calculate Fiscal year based on date
Hi,

If your fiscal year starts on September 1 (4 months before the calendar year) use:
``````TRUNC ( ADD_MONTHS (effective_year, 4)
, 'YEAR'
)``````
The "magic number" 4 relects that your year starts 4 months before the calendar year.

For example, to count rows by fiscal year:
``````SELECT    TO_CHAR ( TRUNC ( ADD_MONTHS (effective_date, 4)
, 'YEAR'
)
, 'YYYY'
)          AS fiscal_year
,       COUNT (*)          AS num_rows
FROM       table_x
GROUP BY  TRUNC ( ADD_MONTHS (effective_date, 4)
, 'YEAR'
)
ORDER BY  fiscal_year
;``````
Fiscal year 2011 will be the year ending on August 31, 2011.

Edited by: Frank Kulash on May 9, 2011 11:18 AM
• ###### 2. Re: How to calculate Fiscal year based on date
Our fiscal year starts on the 1st August through to the 31st July
• ###### 3. Re: How to calculate Fiscal year based on date
Hi,
oraCraft wrote:
Our fiscal year starts on the 1st August through to the 31st July
Okay, your fiscal year starts 5 months before the beginning of the calendar year, not 4. Wherever I used the magic number 4, you'll want to use 5 instead.
• ###### 4. Re: How to calculate Fiscal year based on date
oraCraft wrote:
Our fiscal year starts on the 1st August through to the 31st July
SY.
• ###### 5. Re: How to calculate Fiscal year based on date
My Fiscal year is from April to March. The following works for me.

--
declare
testdate date := '01-JAN-2011';
FY varchar2(4);

begin

select case to_char(testdate,'MM')
WHEN '01' THEN to_char(to_date(testdate), 'YYYY')
WHEN '02' THEN to_char(to_date(testdate), 'YYYY')
WHEN '03' THEN to_char(to_date(testdate), 'YYYY')
WHEN '04' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '05' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '06' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '07' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '08' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '09' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '10' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '11' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
WHEN '12' THEN to_char(to_number(to_char(to_date(testdate), 'YYYY')) + 1)
end
INTO FY
from dual;

dbms_output.put_line('FY='||FY);
end;