Calculating Fiscal Year

Database

Calculating Fiscal Year

Hi all,

I need to create a query for for Fiscal year calculation. The followings are my requirements and expected results.

  • Fiscal year is starting from first Sunday of Feb month in every year.

                The following table refers the starting fiscal year

First day fiscal year

Year

6/2/2011

2011

5/2/2012

2012

3/2/2013

2013

2/2/2014

2014

1/2/2015

2015

7/2/2016

2016

 

Expected Results :

  • If current date is 30-MAY-2015(day from fiscal year 2015), then foundation should have data from 05-Feb-2012 (first day of fiscal year 2012) till current date. So we will have full fiscal year data for 2012, 2013, 2014.
  • If current date is 1-feb-2014 (Last day of fiscal year of 2013), then foundation should have data from 06-Feb-2011 (First day of fiscal year 2011) till current date. So we will have full fiscal year data for 2011, 2012, 2013.
  • If current date is 31-Jan-2015 (last day of fiscal year 2014), then foundation should have data from 05-Feb-2012 (first day of fiscal year 2012) till current date. So we will have full fiscal year data for  2012, 2013,2014.
  • If current date is 1-feb-2015 (starting day of fiscal year 2015), then foundation should have data from 05-Feb-2012 (first day of fiscal year 2012) till current date. So we will have full fiscal year data for  2012, 2013,2014.

I can able to find the 1 day of feb ,But after that i am not able to find the first sunday. Please help me on this requirement. 

select cast(cast((select( '01' ||'02'||trim((select EXTRACT(YEAR FROM (select CURRENT_DATE - INTERVAL '3' YEAR as OLD_DATE)))))) as date format 'ddmmyyyy') as date format 'yyyy-mm-dd') as FEB_FIRST

result :

 FEB_FIRST

 2/1/2012

1 REPLY
Senior Apprentice

Re: Calculating Fiscal Year

Get the next sunday after jan 31:

NEXT_DAY(TO_DATE(TO_CHAR(ADD_MONTHS(CURRENT_DATE, -36), 'YYYY') || '0131', 'yyyymmdd'), 'sun')

You probably need this information regularly, so you should add it as a column to your existing calendar.