Truncate date to fiscal year

11

votes

The following database view truncates the date to the fiscal year (April 1st):

CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
SELECT
  CASE
    WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN    
      to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy')
    ELSE
      to_date('1-APR-'||to_char(SYSDATE, 'YYYY'), 'dd-MON-yyyy')
  END AS fiscal_year
FROM
  dual;

This allows us to calculate the current fiscal year based on today's date.

How can this calculation be simplified or optimized?

sqloracleoracle11gtruncate

12

votes

ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-3),'YYYY'),3)

0

votes

Perhaps this...

SELECT to_date('01/04/' ||
to_char(extract(YEAR FROM SYSDATE)
- CASE WHEN extract(MONTH FROM SYSDATE) BETWEEN 1 AND 4 THEN 1 ELSE 0 END),
'DD/MM/YYYY') FROM dual;

I guess this is another option...

SELECT add_months(trunc(SYSDATE) - extract(DAY FROM SYSDATE) + 1,
- (extract(MONTH FROM SYSDATE) + CASE
WHEN extract(MONTH FROM SYSDATE) <= 4 THEN 12 ELSE 0 END) + 4)
FROM dual;

Other options are rewriting as a function that returns a date, or the logic could be simplified if you could just return the year number for the current fiscal year since you'd only need the logic within the to_char.

0

votes

TRUNC() can be usefully applied to dates, with different format masks. Most pertinently, trunc(sysdate, 'yyyy') gives us the first day of the year. So this will give us the 01-APR of the current year ...

add_months(trunc(sysdate, 'yyyy'), 3)

and this that date for the previous year ...

add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3)

So:

CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
WITH cte as 
    ( select add_months(trunc(sysdate, 'yyyy'), 3) as this_year
             , add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3) as last_year
      from dual )
SELECT
  CASE
    WHEN SYSDATE >= cte.this_year THEN    
      cte.this_year
    ELSE
      cte.last_year
  END AS fiscal_year
FROM
  cte;

caveat: I haven't had the chance to test this code yet so it might contain typos. I will test it later and correct it if necessary.

0

votes

I find the TO_CHAR(date, 'Q') feature of oracle very useful for calculating fiscal calendars. The query below uses the 'with' clause to build two things

Sample Data - test_dates table.
fiscal_map - a simple mapping of the calendar quarters to your fiscal calendar. In this example the 4th calendar quarter is the 1st fiscal quarter (October 1st).

Example:

with test_dates as (
 select sysdate + level * 80 test_date from dual connect by level < 11
),
fiscal_map as (
 select 1 cal, 2 fiscal from dual
 union
 select 2 cal, 3 fiscal from dual
 union
 select 3 cal, 4 fiscal from dual
 union
 select 4 cal, 1 fiscal from dual
)
select 
 test_date, 
 TO_CHAR(test_date, 'Q') cal_quarter, 
 fiscal_map.fiscal,
 (case when CAL < fiscal then 
     TO_CHAR(test_date, 'yyyy') + 0
     else TO_CHAR(test_date, 'yyyy') + 1
 end) FISCAL_YEAR
from test_dates, fiscal_map
where fiscal_map.cal = TO_CHAR(test_date, 'Q')
order by test_date

Output:

TEST_DT CAL_Q   FISCAL Q    FISCAL_YR
22-Jul-10   3   4   2010
10-Oct-10   4   1   2011
29-Dec-10   4   1   2011
19-Mar-11   1   2   2011
07-Jun-11   2   3   2011
26-Aug-11   3   4   2011
14-Nov-11   4   1   2012
02-Feb-12   1   2   2012
22-Apr-12   2   3   2012
11-Jul-12   3   4   2012

0

votes

select T.USERNAME,T.CREATED,

CASE WHEN EXTRACT (MONTH FROM T.CREATED)>=4 AND EXTRACT (MONTH FROM T.CREATED)<=12 THEN 
TO_CHAR(EXTRACT (YEAR FROM T.CREATED))||'-'||TO_CHAR(EXTRACT (YEAR FROM T.CREATED)+1)
  WHEN EXTRACT (MONTH FROM T.CREATED)<4 THEN 
TO_CHAR(EXTRACT (YEAR FROM T.CREATED)-1)||'-'||TO_CHAR(EXTRACT (YEAR FROM T.CREATED)) ELSE NULL END FY
from sys.dba_users t WHERE T.USERNAME in ('101655','100149')

Output will be:

1   101655  14/01/2014 12:21:53 2013-2014
2   100149  05/05/2012 16:55:00 2012-2013

Truncate date to fiscal year

5 Answers