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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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
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).
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,