SQL Server Get the dynamic Fiscal Year if I already have Fiscal Year Start and End Dates in my table?

8fsztsew 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(79)

I am looking for a way to set the Fiscal Year to be dynamic so I won't change it every Fiscal Year Start.

I already have all dates I need in my calendar table as you can see in the picture. Fiscal Year Start Date & Fiscal Year End Date (for all the years)

I can't use ... WHERE FiscalYear = Year(GetDate()) as Jan of all years almost belongs to the previous calendar year ex: 01/01/2024 is still Fiscal Year 2023

I also can't use a case when expression as the start and end of each Fiscal Year is different.

How can I possibly get the dynamic Fiscal Year in my case?

sql-server

来源：https://stackoverflow.com/questions/77174773/get-the-dynamic-fiscal-year-if-i-already-have-fiscal-year-start-and-end-dates-in

1条答案

按热度按时间

z18hc3ub1#

Using your example data:

create table #FiscalYears
(
    FiscalYear int,
    StartDate date,
    EndDate date
)

insert into #FiscalYears values
(2023,'2023-01-29','2024-02-03'),
(2024,'2024-02-04','2025-02-01')

You can get the current Fiscal Year value as follows:

select FiscalYear 
from #FiscalYears
where getdate() >= StartDate
and getdate() <= EndDate

Currently this gives the result 2023 , but once it's 4th Feb 2024 or later (and on or before 1st Feb 2025), it will return 2024 .

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Get the dynamic Fiscal Year if I already have Fiscal Year Start and End Dates in my table?

1条答案

相关问题

热门标签

最新问答