Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: