在SQL Server中计算会计年度。

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

如何从SQL Server中的一个视图中的日期字段计算财政年度?

17 个解决方案

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

#2

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'

它的回报是“2012-07-01 00:00.000”

#3

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.

在这种情况下，财政年度从7/1开始。这是最简单的解。

#4

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

#5

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.

附录:您需要做的是创建一个单独的DB表，其中包含一个财政开始日期，以及每个适用年份的财政结束日期。使用该表中的数据计算给定日期的财政年度。

#6

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

你应该检查你的财政年度的定义，因为每个公司都不同

#7

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

@ fiscal yearalstartmonth是您的财政年度开始月(数字)，@Date是相关的日期，请执行以下操作:

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

您可以在函数中取消此操作，或者在派生视图中作为列使用

#8

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

本例最简单的表达式:年份(DATEADD(月，3，日期))

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.

财政年度是联邦*的会计期间。它从10月1日开始，到下一个日历年的9月30日结束。每一个财政年度都由它结束的历年确定，通常被称为“财政年度”。例如，2003年10月1日开始，2003年9月30日结束……其目的是为国会提供更多的时间来处理拨款立法，特别是为了避免持续的决议。

#9

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

我刚刚意识到Brett Veenstra的明显答案是错误的。难道财政不应该这样计算吗?

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;

#10

    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

#11

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

#12

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

如果需要，用您自己的日期替换getdate()

#13

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

#14

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

我已经扩展了ChrisF和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

#15

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

这是我的版本，在fyyyy - 7/1财年开始时返回财年

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

即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

#16

More simple for Australians :)

对澳大利亚人来说更简单:

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

(一年(返回(月-((DATEPART(月,[日期])+ 5)% 12),[日期]))+)Financial_Year

#17

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

声明@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'))

——财政端选择转换(DATETIME， (CAST(YEAR(@DATE) + IIF(MONTH(@DATE) > 6,1,0) AS VARCHAR) + '-6-30'))