Q

Fiscal year, ISO week, part 1

I need to get the ISO week number based upon our fiscal year rather than calendar year. Our fiscal year begins

on September 1 each year and the corresponding 'IW' can be 52, 53 or 1. Saving a more general definition for later, let's say that every week starts on Sunday and ends the following Saturday. The fiscal year begins on the Sunday closest to September 1, which is equivalent to the Sunday following August 28. Given a date and the date of the corresponding fiscal New Year's Day, computing the ISO week is just a matter of counting the days between them and dividing by seven. If the resulting number (disregarding any fractional part) is n, the week is n+1. The trickiest part is determining what is "corresponding fiscal New Year's Day," because, as you pointed out, September 1 may be in week one of the new year, or it may be in week 52 or 53 of the old year. (The same goes for all the dates between August 29 and September 3.)

The function fiscal_week, below, returns the value you're looking for.

CREATE OR REPLACE FUNCTION  fiscal_new_year

--  *************************************
--  **  f i s c a l _ n e w _ y e a r  **
--  *************************************

--  fiscal_new_year returns the date of the
--  start of the most recent fiscal year on
--  or before in_dt.

--  FUNCTION  fiscal_new_year
(
    in_dt  IN  DATE
)
RETURN  DATE
DETERMINISTIC
AS
    fny_dt     DATE;  -- candidate for being returned
    year_text  CHAR (4) := TO_CHAR (in_dt, 'YYYY');
BEGIN
    -- fny may be in the same calendar year or the previous one
    FOR try_year IN 1 .. 2
    LOOP
        -- fny is the first Sunday after August 28
        fny_dt := NEXT_DAY ( TO_DATE ( year_text || '0828',
                                       'YYYYMMDD'
                                     ),
                             'SUNDAY' -- First day of the week
                           );
        IF  fny_dt <= in_dt
        THEN  -- This guess was correct
            EXIT;
        END IF;

        -- in_dt is before fny_dt; get set to try previous year
        year_text := TRIM ( TO_CHAR ( TO_NUMBER ( year_text,
                                                  '9999'
                                                ) - 1,
                                      '9999'
                                    )
                          );
    END LOOP;

    RETURN  fny_dt;
END  fiscal_new_year;
/

CREATE OR REPLACE FUNCTION  fiscal_week

--  *****************************
--  **  f i s c a l _ w e e k  **
--  *****************************

--  fiscal_week returns an integer (1-53) indicating
--  which week of the fiscal year in_dt is in.

--  FUNCTION  fiscal_week
(
    in_dt  IN  DATE
)
RETURN  PLS_INTEGER
DETERMINISTIC
AS
BEGIN
    RETURN  1 + FLOOR ((in_dt - fiscal_new_year (in_dt)) / 7);
END  fiscal_week;
/

Continued in part 2.

This was first published in September 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close