Fiscal Year dynamic function (Oracle SQL)

Question

My Fiscal Year starts on 1st October (2017) and ends 30th September (2018)

I want to write a function that will dynamically calculate first day of October.

Currently I have:

select Add_Months(Trunc(Add_Months(sysdate,3),'YYYY'), -3) from dual

• Philᵀᴹ: Do you mean you want the 1st day of the fiscal year for the current period? So if today was 2016年01月01日, you'd want 2015年10月01日 ?
• marcin2x4: @PhilTM - yes, exactly.

Answer 1

Lots of ways of doing this. But here's one:

-- should be 2012
SELECT (EXTRACT (YEAR FROM ADD_MONTHS (DATE '2013-09-30', -9))||'-10-01') from dual;
-- should be 2013
SELECT (EXTRACT (YEAR FROM ADD_MONTHS (DATE '2013-10-01', -9))||'-10-01') from dual;
-- should be 2018
SELECT (EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -9))||'-10-01') from dual;

Just needs a TO_DATE().

Fiddle link.

• marcin2x4: Thanks! - it also works. I had to re-arrange to meet my date format. SELECT '01-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(to_date('01-OCT-19'), -9))) from dual, SELECT '01-OCT-'||(EXTRACT (YEAR FROM ADD_MONTHS(sysdate, -9))) from dual

Answer 2

create or replace function fiscal_year_start_date(p_date date) return date
as
 pragma udf;
begin
 return add_months(trunc(add_months(p_date,3),'YYYY'), -3);
end;
/
alter session set nls_date_format='YYYY-MM-DD';
select fiscal_year_start_date(date'2016-01-01') from dual;
FISCAL_YEA
----------
2015年10月01日

pragma_udf is optional, it is a 12c feature that boosts performance of such user-defined functions a bit when used in plain SQL (but may degrade in PL/SQL).

Answer 3

If you need a more dynamic function that wouldn't require you to write logic for each year, you could use this CASE WHEN statement below that will work for any year given a set FY (in this Oct. 1 is the start of the FY). Essentially it, reads the month of the date field and if it's from Jan - Sept, then it extracts the year as is from the date field and creates a the new FY variable. If the month is from Oct - Dec, it takes the year and adds 1 to it. Obviously this logic can be adjusted for the specific organizations business definition of how they define the FY, but I hope you can understand the logic.

CASE
 WHEN EXTRACT(MONTH FROM k.date_var) BETWEEN 10 AND 12 THEN EXTRACT(YEAR FROM date_var) +1 
 WHEN EXTRACT(MONTH FROM k.date_var) BETWEEN 1 AND 9 THEN EXTRACT(YEAR FROM k.date_var) 
 ELSE NULL
 END AS FY_CONCLUDED,

• peterh: Welcome on the DBA SE! I suggest to write also a textual explanation, what your snippet does and how.