DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Fiscal Year Function tuning.

  1. #1
    Join Date
    Feb 2003
    Posts
    6

    Fiscal Year Function tuning.

    I created the following function to return the first day of the fiscal year of the P_IN_DATE passed to it. The second parameter is only used if there is a max day you want to use.

    This works fine, but is not as fast as I would like. Any ideas on how to improve this?

    CREATE OR REPLACE FUNCTION FN_FIRST_DAY_FY
    (P_IN_DATE DATE := TRUNC(SYSDATE)
    ,P_MAX_DT DATE := TRUNC(SYSDATE))
    RETURN DATE
    IS
    v_in_YYYY number;
    v_current_YYYY number;

    BEGIN
    v_in_YYYY := TO_NUMBER(TO_CHAR(add_months(p_in_date, 3), 'YYYY'));
    v_current_YYYY := TO_NUMBER(TO_CHAR(add_months(P_MAX_DT, 3), 'YYYY'));
    if v_in_YYYY >= v_current_YYYY
    then
    return to_date('01-OCT-'||to_char(v_current_YYYY - 1), 'DD-MON-YYYY');
    else
    return to_date('01-OCT-'||to_char(v_in_YYYY - 1), 'DD-MON-YYYY');
    end if;
    END;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Here's a starting point ...
    Code:
    CREATE OR REPLACE FUNCTION FN_FIRST_DAY_FY
    RETURN DATE
    Is
    Begin
    Return Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3);
    End;
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by slimdave
    Here's a starting point ...
    Code:
    CREATE OR REPLACE FUNCTION FN_FIRST_DAY_FY
    RETURN DATE
    Is
    Begin
    Return Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'),-3);
    End;
    /
    Now that is pretty slick!

  4. #4
    Join Date
    Feb 2003
    Posts
    6
    Slim I a little past the starting point. Just see if there is a way to improve speed.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by bmullin57
    Slim I a little past the starting point. Just see if there is a way to improve speed.
    The code that Slim Dave gave you should be pretty fast.
    If there is a performance issue, it probably isn't this code or even your code that you posted.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    To improve performance, don't call it as a function from SQL, just use the SQL expression that it contains. Otherwise I don't think you'd get any method faster than that
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width