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

    How to calculate Fiscal year based on date

    SelectStaR
      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
          Frank Kulash
          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
            SelectStaR
            Our fiscal year starts on the 1st August through to the 31st July
            • 3. Re: How to calculate Fiscal year based on date
              Frank Kulash
              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
                Solomon Yakobson
                oraCraft wrote:
                Our fiscal year starts on the 1st August through to the 31st July
                So add 5 months:
                TO_CHAR(TRUNC(ADD_MONTHS(dt,5),'yyyy'),'yyyy') fiscal_year
                SY.
                • 5. Re: How to calculate Fiscal year based on date
                  285499
                  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;