sql - 如何使用 SQL 选择查询按月计算印度财政年度？

Question

我需要如下格式的财政年度。在印度的财政年度从每年1 April到31 March每年。

1. Example 1 April 2018 to 31 march 2019 (2018 - 2019).
2. Example 1 April 2019 to 31 march 2020 (2019 - 2020).

我想在输出下方并选择从 2018 年到当前日期的列下方需要查询，如果新的 4 月开始，那么新的下拉值应该再次出现，例如2020过去一年，那么如果当前月份April 2021，那么新的下拉值2021 - 2022应该像这样出现。

Year       FinancialYear
------------------------
2018       2018-2019 
2019       2019-2020

标签： sqlsql-serversql-server-2008-r2

Answer 1

您可以尝试使用datefromparts()如下所示的功能。这是DATEFROMPARTS (Transact-SQL)的官方文档。

此函数返回映射到指定年、月和日值的日期值。

句法

DATEFROMPARTS ( year, month, day )

以下是为您提供的实现。

create table FinancialYear (Year int)
insert into FinancialYear values (2018), (2019), (2020)

Select Year, 
       Cast(Year as Varchar(4)) + '-' + Cast(year + 1 as varchar(4)) as FinancialYear
       ,datefromparts(year, 4, 1) as [StartingDate]
       ,datefromparts(year + 1 , 3, 31) as [EndingDate] 
from FinancialYear

现场演示

输出如下图所示。

有一个替代方法是使用cte你不需要的表，如下所示。

-- Define start and end limits
Declare @start int, @end int
Select @start = 2018, -- As you want
@end = year(getdate()) --Current Year

;With NumberSequence( Year ) as
(
    Select @start as Year
        union all
    Select Year + 1
        from NumberSequence
        where Year < @end
)

--Select result
Select Year, 
       Cast(Year as Varchar(4)) + '-' + Cast(year + 1 as varchar(4)) as FinancialYear
       ,datefromparts(year, 4, 1) as [StartingDate]
       ,datefromparts(year + 1 , 3, 31) as [EndingDate] 
from NumberSequence Option (MaxRecursion 1000)

这是现场演示。

编辑

如果您使用的是 SQL Server 2005，那么您的查询将如下所示。

insert into FinancialYear values (2018)
insert into FinancialYear values (2019)
insert into FinancialYear values (2020)

Select Year, 
       Cast(Year as Varchar(4)) + '-' + Cast(year + 1 as varchar(4)) as FinancialYear
       ,CAST(Convert(Varchar(4), year) + '-' + '4' + '-' + '1' as DATETime) as [StartingDate]
       ,CAST(Convert(Varchar(4), year + 1) + '-' + '3' + '-' + '31' as DateTime) as [EndingDate] 
from FinancialYear

如果您想排除月份编号为 的财政年度Jan，Fab或者March您可以编写 where 条件，如下所示。

Select Year, 
       Cast(Year as Varchar(4)) + '-' + Cast(year + 1 as varchar(4)) as FinancialYear
       ,CAST(Convert(Varchar(4), year) + '-' + '4' + '-' + '1' as DATETime) as [StartingDate]
       ,CAST(Convert(Varchar(4), year + 1) + '-' + '3' + '-' + '31' as DateTime) as [EndingDate] 
from FinancialYear
where (
    (ISNULL(month(getdate()), 0) >= 4 ) 
    OR 
    (ISNULL(month(getdate()), 0) < 4 AND Year < year(getdate()))
    )