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;
/

---

Quote:

---

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.

Quote:

---

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