Q
Problem solve Get help with specific problems with your technologies, process and projects.

Fiscal year, ISO week, part 2

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.

Continued from part 1.

Click for general definition of a week.

Sometimes (probably) you will want a formatted string that indicates the date, analogous to TO_CHAR (in_dt, 'IYYY-IW-D'). The function below returns such a string. It's convenient the same numbers to refer to calendar years and fiscal years. This function labels the fiscal year according to the calendar year in which it ends. (That is, the fiscal year that started on August 31, 2003 and ends with August 28, 2004 is called "2004".) That makes sense, considering that more days in your fiscal 2004 are in calendar 2004 than in calendar 2003. That also seems to be the convention regardless of when the fiscal year begins. (My company started fiscal 2004 on July 1, even though most of the days in that fiscal year are in calendar year 2003.)

CREATE OR REPLACE FUNCTION  fiscal_to_char

--  ***********************************
--  **  f i s c a l _ t o _ c h a r  **
--  ***********************************

--  fiscal_to_char returns a 10-character string in the format
--  "yyyy-Fww-d" where
--      yyyy is the fiscal year number (last calendar year in fiscal year)
--      ww          number of weeks into the year (01-53)
--      d           number of the week day (1-7)

-- FUNCTION  fiscal_to_char
(
    in_dt  IN  DATE
)
RETURN  CHAR
DETERMINISTIC
AS
    fny_dt     DATE := fiscal_new_year (in_dt);
    day_cnt    PLS_INTEGER := TRUNC (in_dt) - fny_dt;
BEGIN
    RETURN  TO_CHAR (fny_dt + 365, 'YYYY')   -- End of fiscal year
       ||   '-F'
       ||   TRIM ( TO_CHAR ( 1 + FLOOR (day_cnt / 7),
                             '00'
                           )
                 )
       ||   '-'
       ||   TRIM ( TO_CHAR ( 1 + MOD (day_cnt, 7),
                             '9'
                           )
                 );
END  fiscal_to_char;
/

What Is a Week?

In the United States, Sunday is generally considered the first day of the week. In other parts of the world, Monday is considered the first day of the week. (In some eastern European languages, the words for Monday through Friday are literally "first day", "second day", etc.) The Oracle TO_CHAR function with format mask 'D' reflects this. The parameter NLS_TERRITORY governs what is considered to be the first day of the week.

SQL>  ALTER SESSION SET nls_territory = 'AMERICA';
Session altered.

SQL>  SELECT TO_CHAR (SYSDATE, 'D') FROM dual;

T
-
2

SQL>  ALTER SESSION SET nls_territory = 'LITHUANIA';
Session altered.

SQL>  SELECT TO_CHAR (SYSDATE, 'D') FROM dual;

T
-
1

In the fiscal_new_year function above, 'SUNDAY' is hard-coded as the first day of the week. If you want Monday (or any other day) to be first, you could simply replace that one hard-coded value in the fiscal_new_year function. All the functions would behave the same regardless of the NLS_TERRITORY setting. What if you want to follow the local custom, as indicated by NLS_TERRITORY? Replace the literal in fiscal_new_year with a call to this function:

CREATE OR REPLACE FUNCTION  first_day

--  *************************
--  **  f i r s t _ d a y  **
--  *************************

--  first_day returns the name (as defined by NLS_DATE_LANGUAGE)
--  of the first day of the week (as defined by NLS_TERRITORY).

-- FUNCTION  first_day
RETURN  VARCHAR2
DETERMINISTIC
AS
    try_dt   DATE := SYSDATE;  -- Start with any random day
BEGIN
    FOR  try_num  IN  1 .. 7
    LOOP
        IF  TO_CHAR (try_dt, 'D') = 1
        THEN
            RETURN  TO_CHAR (try_dt, 'DAY');
        END IF;

        -- That wasn't it.  Try the next day
        try_dt := try_dt + 1;
    END LOOP;
END  first_day;
/

Another potential advantage of using the first_day function is that it removes any chance of getting a "ORA-01846: not a val

Dig Deeper on Using Oracle PL-SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close