calculate fiscal year in sql select statement?

user_158008 0
Viewed 66058

I have a date field that needs to return in fiscal year format. example

Start_Date        Year 
04/01/2012 -      2013
01/01/2012 -      2012
09/15/2013 -      2014

We need to calculate 

04/01/2012 to 03/31/2013 is FY 2013

and 

04/01/2013 to 03/31/2014 is FY 2014

How can we do that in select statement?

tsql sql-server sql
11 Answers

For only year format (FY 2020) use following query:

SELECT tender_opening_date,CASE WHEN MONTH( order_date) >= 4
            THEN CONCAT('FY ',YEAR(( order_date)) +1  )
            ELSE CONCAT('FY ',YEAR( order_date)-1   )
       END AS Fiscal_Year
FROM orders_tbl

For full financial Year (FY 2019-20) use following query:

SELECT tender_opening_date,CASE WHEN MONTH( order_date) >= 4
                THEN CONCAT('FY ',YEAR( order_date),'-',DATE_FORMAT( order_date,'%y') +1  )
                ELSE CONCAT('FY ',YEAR( order_date)-1,'-',DATE_FORMAT( order_date,'%y')   )
           END AS Fiscal_Year
    FROM orders_tbl
user_58039740

For Australian financial year, use the following:

SELECT
    year(dateadd(MONTH, 6, DateColumn)) AS FY,
    ...
FROM ...
user_92284370
Related