calculate fiscal year in sql select statement?

Question

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?

Answer 1

David has a very good solution. A simpler expression is:

select year(dateadd(month, -3, start_date)) as FiscalYear

That is, subtract 3 months and take the year.

EDIT:

As noted in the comment, this seems to produce one year too early. Here are two solutions:

select year(dateadd(month, 9, start_date)) as FiscalYear

select 1 + year(dateadd(month, -3, start_date)) as FiscalYear

Answer 2

SELECT CASE WHEN DatePart(Month, Start_Date) >= 4
            THEN DatePart(Year, Start_Date) + 1
            ELSE DatePart(Year, Start_Date)
       END AS Fiscal_Year
FROM data