Calculate fiscal year in SQL Server

Question

How would you calculate the fiscal year from a date field in a view in SQL Server?

Answer 1

CASE WHEN MONTH(@Date) > 10 THEN YEAR(@Date) + 1 ELSE YEAR(@Date) END

Answer 2

More simple for Australians :)

(YEAR(DATEADD(Month,-((DATEPART(Month,[Date])+5) %12),[Date]))+) AS Financial_Year

Answer 3

I don't think you can, because there is no universal fiscal calendar. Fiscal years vary between businesses and countries.

ADDENDUM: What you would need to do is have a separate DB table consisting of a fiscal start date, and a fiscal end date for each applicable year. Use the data in that table to calculate the fiscal year given a particular date.

Answer 4

Here's my version which returns fiscal year as FYyyyy - fiscal year begins 7/1

i.e. 6/1/2015 -> FY1415, 7/1/2015 -> FY1516

String functions could be better...

        CREATE FUNCTION [dbo].[FY](@DATE DATETIME)
        RETURNS char(6)
        AS
        BEGIN
            DECLARE @Answer     char(6)
            SET @Answer =    
            CASE WHEN MONTH(@DATE) < 7 
                 THEN 'FY' + RIGHT(CAST(YEAR(@DATE) - 1 AS VARCHAR(11)), 2) + RIGHT(CAST(YEAR(@DATE) AS VARCHAR(11)), 2) 
                 ELSE 'FY' + RIGHT(CAST(YEAR(@DATE) AS VARCHAR(11)), 2) + RIGHT(CAST(YEAR(@DATE) + 1 AS VARCHAR(11)), 2) END
            RETURN @Answer
        END

Answer 5

Start of fiscal year:

DATEADD(MONTH, DATEDIFF(MONTH, '20100401', getdate()) / 12 * 12, '20100401')

End of Fiscal Year:

DATEADD(MONTH, DATEDIFF(MONTH, '20100401', getdate()) / 12 * 12, '20110331')

Replace getdate() with your own date if required

Answer 6

I've extended the answer posted by ChrisF and Conficker.

DECLARE @FFYStartMonth INT = 10 --The first month of the FFY
DECLARE @EntryDate DATETIME = '4/1/2015' --The date of the data
DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(dd, 0,
    DATEDIFF(dd, 0,
        DATEADD(mm, - (((12 + DATEPART(m, @EntryDate)) - @FFYStartMonth)%12), @EntryDate) -
datePart(d,DATEADD(mm, - (((12 + DATEPART(m, @EntryDate)) - @FFYStartMonth )%12),
    @EntryDate )) + 1 ))  

SET @EndDate = DATEADD(SS, -1, DATEADD(mm, 12, @StartDate))

SELECT @StartDate, @EndDate