1

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

  • 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 ? – Philᵀᴹ Nov 15 '18 at 15:11
  • @Philᵀᴹ - yes, exactly. – marcin2x4 Nov 15 '18 at 15:14
3

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.

  • 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 – marcin2x4 Nov 16 '18 at 7:34
4
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).

1

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,
  • Welcome on the DBA SE! I suggest to write also a textual explanation, what your snippet does and how. – peterh - Reinstate Monica Jan 30 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.