-
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;
-
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;
/
-
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!
-
Slim I a little past the starting point. Just see if there is a way to improve speed.
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|