当前位置：文江博客话题详情

在 SQL Server 中计算会计年度

发布于 2024-08-12 03:48:04 字数 38 浏览 4 评论 0原文

如何根据 SQL Server 视图中的日期字段计算会计年度？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜是你 2024-08-19 03:48:04

我建议您根据申请的财政年度使用用户定义的函数。

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

像这样使用它：

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


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

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

回复收藏 0 原文

寄意 2024-08-19 03:48:04

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

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

回复收藏 0 原文

不打扰别人 2024-08-19 03:48:04

这是澳大利亚财政年度开始日期代码，

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

它返回类似 '2012-07-01 00:00:00.000'

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'

回复收藏 0 原文

高速公鹿 2024-08-19 03:48:04

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

在这种情况下，财政年度从 7 月 1 日开始。这是最简单的解决方案。

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.

回复收藏 0 原文

寒冷纷飞旳雪 2024-08-19 03:48:04

我扩展了 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

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

回复收藏 0 原文

失与倦＂ 2024-08-19 03:48:04

这种情况的最简单表达：
YEAR(DATEADD(month, 3, Date))

联邦财政年度

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

这可能不适用于美国以外的其他国家和地区，但您只需根据需要替换数字3即可。

回复收藏 0 原文

套路撩心 2024-08-19 03:48:04

我认为你不能，因为没有通用的财政日历。不同企业和国家的财政年度各不相同。

附录：您需要做的是拥有一个单独的数据库表，其中包含每个适用年份的会计开始日期和会计结束日期。使用该表中的数据计算给定特定日期的会计年度。

回复收藏 0 原文

清引 2024-08-19 03:48:04

您需要多个字段来执行此操作...

您应该检查会计年度的定义，因为它因公司而异

回复收藏 0 原文

流心雨 2024-08-19 03:48:04

给定的 @FiscalYearStartMonth 是您的财政年度开始月份（数字）
并且 @Date 是有问题的日期，请执行以下操作：

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

您可以在函数中将其抽象化，或用作派生视图中的列

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

回复收藏 0 原文

梦晓ヶ微光ヅ倾城 2024-08-19 03:48:04

我刚刚意识到布雷特·文斯特拉的标记答案是错误的。 FY不应该这样计算吗？：

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;

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;

回复收藏 0 原文

月牙弯弯 2024-08-19 03:48:04

基于 @csaba-toth 的上述答案，假设您的财政年度从该月的第一天开始

year(dateadd(month, (12 -FyStartMonth + 1),)

我的财政年度从 1 月 1 日开始，即第 7 个月，所以我的常量是 (12 - 7 + 1 =) 6.

测试用例（如2019 年 9 月 25 日）：

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

2020    2020    2021    2020

我确实相信这是最简单、也许也是最容易理解的实现。

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.

回复收藏 0 原文

苏佲洛 2024-08-19 03:48:04

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'

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'

回复收藏 0 原文

一直在等你来 2024-08-19 03:48:04

对于过去一年且开始日期为 oct 1st 10-1-2021

代码：

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

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

回复收藏 0 原文

稀香 2024-08-19 03:48:04

    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

    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

回复收藏 0 原文

在你怀里撒娇 2024-08-19 03:48:04

这是英国的动态代码，

您可以根据不同的需求进行修改，

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

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

回复收藏 0 原文

红颜悴 2024-08-19 03:48:04

会计年度开始：

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

会计年度结束：

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

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

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

回复收藏 0 原文

两个我 2024-08-19 03:48:04

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

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

回复收藏 0 原文

余罪 2024-08-19 03:48:04

这是我的版本，它返回财政年度为 FYyyyy - 财政年度从 7/1 开始

，即 6/1/2015 -> FY1415，2015 年 7 月 1 日 -> FY1516

字符串函数可以更好...

        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

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

回复收藏 0 原文

旧情勿念 2024-08-19 03:48:04

如果您只想在查询中活动/当前会计年度，您可以使用类似以下内容：

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

您也可以这样使用：

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

获取当前会计年度的开始/结束日期。（这是基于 10 月 31 日的年末，但显然可以根据需要进行调整。）然后可以在这样的查询中使用它，以便您的报告自动调整为活动会计年度：

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

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

回复收藏 0 原文

世界如花海般美丽 2024-08-19 03:48:04

我只是不得不这样做，发现现有的答案很难理解，所以这里有一个针对英国的详细解释。

英国计算

说明：英国标准财政年度于 4 月 5 日结束，新财政年度于 4 月 6 日开始。然而，公司可以改变这一点，因此您的代码可能需要调整。

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

如果按原样运行，则会得到 2019 年 - 4 月 6 日是 2019 财政年度的第一个日期。

更改为 4 月 5 日，它将返回 2018 年。

解释

其工作方式是将您查看的日期向后移动，以便财政年度结束为正常年终 - 即 12 月 31 日 - 然后正常地从中获取年份。

在查询中使用内联很容易。如果您需要代码来处理不同的财务年度定义，您可以将 -5 和 -3 放入参数中，然后简单地传入它们。

日期偏移量为 -(财政年度第一天的月份 - 1)

月份偏移量为 -(财政年度第一天的月份 - 1)

注意：如果您的财政年度开始于首先，日期偏移量是 -(1 - 0) - 或 -0 或者当然是 0。

在这种情况下，你可以完全消除这种情况，但我认为如果为了一致性而保留它会更清楚。

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.

回复收藏 0 原文