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

I suggest you use a User-Defined Function based on the Fiscal year of your application.

CREATE FUNCTION dbo.fnc_FiscalYear(
    @AsOf           DATETIME
)
RETURNS INT
AS
BEGIN

    DECLARE @Answer     INT

    -- You define what you want here (September being your changeover month)
    IF ( MONTH(@AsOf) < 9 )
        SET @Answer = YEAR(@AsOf) - 1
    ELSE
        SET @Answer = YEAR(@AsOf)


    RETURN @Answer

END



GO

Use it like this:

SELECT dbo.fnc_FiscalYear('9/1/2009')


SELECT dbo.fnc_FiscalYear('8/31/2009')

Answer 2

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

Answer 3

Here is Australian Financial year start date code

 select DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm,
 -(((12 + DATEPART(m, getDate())) - 7)%12), getDate() ) 
 - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 7)%12),getDate() ))+1 ) )

It returns like '2012-07-01 00:00:00.000'

Answer 4

CASE 
  WHEN MONTH(Date) > 6 
   THEN YEAR(Date) + 1
   ELSE YEAR(Date)
  END AS [FISCAL YEAR]

In this case, Fiscal Year starts on 7/1. This is the simplest solution out there.

Answer 5

Simplest expression for this case: YEAR(DATEADD(month, 3, Date))

The Federal Fiscal Year

The fiscal year is the accounting period of the federal government. It begins on October 1 and ends on September 30 of the next calendar year. Each fiscal year is identified by the calendar year in which it ends and commonly is referred to as "FY." For example, FY2003 began October 1, 2002, and ends September 30, 2003... the intent was to provide Congress with more time to process appropriations legislation, particularly to avoid continuing resolutions.

This may not apply to other countries and areas than the US, but you just have to replace the number 3 according to your needs.

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

Answer 7

Given @FiscalYearStartMonth is your fiscal year start month (numeric) and @Date is the date in question, do the following:

SELECT 
  CASE 
      WHEN @FiscalYearStartMonth = 1 OR @FiscalYearStartMonth > MONTH(@Date) 
      THEN YEAR(@Date) 
      ELSE YEAR(@Date) + 1 
  END AS FiscalYear

You can abstact this away in a function, or use as a column in a derived view

Answer 8

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 9

You would need more than a single field to do this...

You should check your definition of fiscal year as it varies from company to company

Answer 10

I just realized that the marked answer by Brett Veenstra is wrong. Shouldn't The FY should be calculated like this?:

CREATE FUNCTION dbo.fnc_FiscalYear(
    @AsOf           DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Answer     INT
    IF ( MONTH(@AsOf) < 9 )
        SET @Answer = YEAR(@AsOf) 
    ELSE
        SET @Answer = YEAR(@AsOf) + 1
    RETURN @Answer
END;

Answer 11

Building on the answer above by @csaba-toth, and assuming your fiscal year starts on the first day of the month

year(dateadd(month, (12 -FyStartMonth + 1), <date>)

My FY starts 1-July, the 7th month, so my constant is (12 - 7 + 1 =) 6.

Test cases (as of 25-Sep-2019):

select year(dateadd(month, 6, getdate()))
, year(dateadd(month,6, '1/1/2020'))
, year(dateadd(month, 6, '7/1/2020'))
, year(dateadd(month, 6, '6/30/2020'))

Returns:

2020    2020    2021    2020

I do believe this is the simplest and perhaps most comprehensible implementation.

Answer 12

DECLARE @DateFieldName DATETIME = '1/1/2020'

--UK Fiscal Year

SELECT 
CASE 
  WHEN MONTH(@DateFieldName) in (1,2,3)
   THEN CONCAT(YEAR(@DateFieldName) -1 , '-' , YEAR(@DateFieldName) )
   ELSE CONCAT(YEAR(@DateFieldName) , '-' , YEAR(@DateFieldName)+1 )  
  END AS [FISCAL YEAR]

--RESULT = '2019-2020'

Answer 13

For one year in the past and a start date of oct 1st 10-1-2021

Code:

CAST(CONVERT (varchar(4),YEAR(GetDate())-1) + '-' + '10' + '-' + '01' AS Datetime2(0))   

Answer 14

    declare 
@InputDate datetime,
@FiscalInput varchar(2),
@FiscalYear varchar(4),
@FiscalMonth varchar(2),
@FiscalStart varchar(10),
@FiscalDate varchar(10)

set @FiscalInput = '10'
set @InputDate = '1/5/2010'
set @FiscalYear = (select 
                    case 
                    when datepart(mm,@InputDate) < cast(@FiscalInput as int)
                        then datepart(yyyy, @InputDate)
                    when datepart(mm,@InputDate) >= cast(@FiscalInput as int)
                        then datepart(yyyy, @InputDate) + 1
                        end FiscalYear)


set @FiscalStart = (select @FiscalInput + '/01/' + @FiscalYear)

set @FiscalDate = (select cast(datepart(mm,@InputDate) as varchar(2)) + '/' + cast(datepart(dd,@InputDate) as varchar(2)) + '/' + @FiscalYear)
set @FiscalMonth = (select 
                    case 
                    when datepart(mm,@InputDate) < cast(@FiscalInput as int)
                        then 13 + datediff(mm, cast(@FiscalStart as datetime),@InputDate)
                    when datepart(mm,@InputDate) >= cast(@FiscalInput as int)
                        then 1 + datediff(mm, cast(@FiscalStart as datetime), @FiscalDate)
                        end FiscalMonth)    

select @InputDate as Date, 
cast(@FiscalStart as datetime) as FiscalStart, 
dateadd(mm, 11,cast(@FiscalStart as datetime)) as FiscalStop,
cast(@FiscalDate as DateTime) as FiscalDate,
@FiscalMonth as FiscalMonth, 
@FiscalYear as FiscalYear

Answer 15

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 16

DECLARE 
@StartDate DATETIME,
@EndDate DATETIME

if month(getdate())>3
Begin
        set  @StartDate=   convert(datetime, cast(year(getdate())-1 as varchar) + '-4-1')
        set @EndDate= convert(datetime, cast(year(getdate())  as varchar) + '-3-31')

end

else   
begin          
        set @StartDate= Convert(datetime, cast(year(getdate()) - 2 as varchar) + '-4-1')
        set @EndDate= convert(datetime, cast(year(getdate())-1 as varchar) + '-3-31')
end


select @StartDate, @EndDate

Answer 17

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 18

If you just want the active/current Fiscal Year in a query, you can use something like this:

DECLARE @FiscalYear INT
IF ( MONTH(GETDATE()) > 10 )
    SET @FiscalYear = YEAR(GETDATE()) + 1
ELSE
    SET @FiscalYear = YEAR(GETDATE())

Which you can also use like this:

DECLARE @sDate date = (CAST(@FiscalYear-1 AS nvarchar)+'1101')
DECLARE @eDate date = (CAST(@FiscalYear AS nvarchar)+'1031')

To get the start/end date of the current fiscal year. (This is based on a Oct. 31st year end, but can obviously be adjusted as desired.) This can then be used in a query like this so your report is automatically adjusted to the active fiscal year:

SELECT @FiscalYear AS FY, ColumnValues FROM TableName
WHERE InvoiceDate >= @sDate AND InvoiceDate <= @eDate

Answer 19

I just had to do this and found the existing answers hard to follow, so here's a detailed explanation for the UK.

UK Calculation

Note: The standard UK financial year ends on the 5th April, and a new one starts on the 6th April. Companies can however change that so your code may need to be tweaked.

declare @Date datetime
select @Date = '2019-04-06T00:00:00'

select DATEADD(day, -5, DATEADD(month, -3, @Date)) as FinancialEquivalentDate
select YEAR(DATEADD(day, -5, DATEADD(month, -3, @Date))) as FinancialYear

If you run this as-is, you get 2019 - the 6th of April is the first date of the 2019 financial year.

Change to the 5th of April and it will return 2018.

Explanation

The way this works is to shift the date you are looking at back so the financial year end is the normal year end - i.e. 31st December - then take the year from that as normal.

It is easy to use inline in a query. If you needed the code to cope with different financial year definitions you could put the -5 and -3 in parameters and simply pass them in.

The day offset is -(day of month of first day of financial year - 1)

The month offset is -(month of first day of financial year - 1)

Note: If your financial year starts on the first, the day offset is -(1 - 0) - or -0 or of course 0.

You could eliminate that condition entirely in that case but I think it's clearer if left in for consistency.

Answer 20

Here is the dynamic code for UK,

You can work around based on different needs,

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

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

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

SELECT @StartDate, @EndDate

Answer 21

More simple for Australians :)

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

Answer 22

The simple way -

DECLARE @DATE DATETIME = '2016/07/1'
-- Fiscal Start SELECT CONVERT(DATETIME, (CAST(YEAR(@DATE) - IIF(MONTH(@DATE) > 6, 0, 1) AS VARCHAR) + '-7-1'))

-- Fiscal End SELECT CONVERT(DATETIME, (CAST(YEAR(@DATE) + IIF(MONTH(@DATE) > 6, 1, 0) AS VARCHAR) + '-6-30'))