Calculate fiscal year in SQL Server

How would you calculate the fiscal year from a date field in a view in SQL Serve开发者_如何学Pythonr?

---

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')

---

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

---

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'

---

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.

---

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

---

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.

---

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.

---

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

---

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

---

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;

---

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.

---

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'

---

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))   

---

    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

---

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

---

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

---

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

---

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

---

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

---

More simple for Australians :)

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

---

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'))