Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Truncate date to fiscal year

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?

like image 924
Dave Jarvis Avatar asked Apr 20 '10 21:04

Dave Jarvis


1 Answers

ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-3),'YYYY'),3)
like image 178
Jeffrey Kemp Avatar answered Oct 25 '22 21:10

Jeffrey Kemp