在sql select语句中计算财务年度？

I have a date field that needs to return in fiscal year format. example

我有一个日期字段需要以财政年度格式返回。例

Start_Date        Year 
04/01/2012 -      2013
01/01/2012 -      2012
09/15/2013 -      2014

We need to calculate

我们需要计算

04/01/2012 to 03/31/2013 is FY 2013

and

04/01/2013 to 03/31/2014 is FY 2014

How can we do that in select statement?

我们如何在select语句中做到这一点?

9 个解决方案

#1

SELECT CASE WHEN DatePart(Month, Start_Date) >= 4
            THEN DatePart(Year, Start_Date) + 1
            ELSE DatePart(Year, Start_Date)
       END AS Fiscal_Year
FROM data

#2

David has a very good solution. A simpler expression is:

大卫有一个很好的解决方案。一个更简单的表达是:

select year(dateadd(month, -3, start_date)) as FiscalYear

That is, subtract 3 months and take the year.

也就是说,减去3个月并取一年。

EDIT:

As noted in the comment, this seems to produce one year too early. Here are two solutions:

正如评论中所指出的,这似乎过早产生了一年。这有两个解决方案:

select year(dateadd(month, 9, start_date)) as FiscalYear

select 1 + year(dateadd(month, -3, start_date)) as FiscalYear

#3

I just chose to do it this way. It's easy and you can replace the 09,30 with whatever you want your fiscal year end to be. Not sure if this works in anything but SQL Server though.

我只是选择这样做。这很容易,你可以用你想要的财政年度结束来取代09,30。不确定这是否适用于除SQL Server之外的任何内容。

CASE 
    WHEN CAST(GETDATE() AS DATE) > 
         SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000) 
    THEN 
        DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE()) 
    END AS FY

#4

Assuming that you have a table Fiscal_Year with Start_Date per Year, and you want to find the fiscal year for a given date @Date:

假设您有一个表Fiscal_Year,每年的Start_Date,并且您想要查找给定日期@Date的会计年度:

SELECT MIN(Year) WHERE @Date >= Start_Date FROM Fiscal_Year

#5

Declare @t table(StartDate date ,Year1 int)
insert into @t values('04/01/2012',2013),('01/01/2012',2012),('09/15/2013',2014)
;with CTE as
(select max(year1) maxyear from @t)
, cte1 as
 (select cast('04/01/'+convert(varchar(4),a.year1) as  date) FromFY,dateadd(day,-1,dateadd(year,1,cast('04/01/'+convert(varchar(4),a.year1) as date))) ToFY


,b.year1 from @t a  inner join (select min(year1)year1 from @t) b on a.year1=b.year1
    union all
   select cast(dateadd(day,1,ToFY) as date),cast(dateadd(year,1,ToFY) as date),year1+1 year1 from     cte1 where year1<=(select maxyear from cte)
)

select * from cte1

#6

DECLARE @STARTDATE DATETIME, @ENDDATE DATETIME,@CURR_DATE DATETIME
SET @CURR_DATE='2015-01-30'
IF MONTH(@CURR_DATE) IN (1,2,3)
BEGIN
    SET @STARTDATE= CAST( CAST(YEAR(@CURR_DATE)-1 AS VARCHAR)+'/04/01'  AS DATE)
    SET @ENDDATE= CAST( CAST(YEAR(@CURR_DATE)  AS VARCHAR)+'/03/31'  AS DATE)
END
ELSE
BEGIN
    SET @STARTDATE= CAST( CAST(YEAR(@CURR_DATE) AS VARCHAR)+'/04/01'  AS DATE)
    SET @ENDDATE= CAST( CAST(YEAR(@CURR_DATE)+1 AS VARCHAR)+'/03/31'  AS DATE)
END
SELECT @STARTDATE AS ST_FI,@ENDDATE AS END_FY

#7

...something simple :)

......简单的东西:)

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

(年(DATEADD(月, - ((DATEPART(月,[日期])+ 5)%12),[日期]))+)AS Financial_Year

#8

declare @Date smalldatetime, @FiscalYearStartMonth tinyint
set @Date = GETDATE();
set @FiscalYearStartMonth = 1; -- Jan

print convert(varchar(4), 
case when MONTH(@Date) < @FiscalYearStartMonth 
then YEAR(@Date) -1 
else YEAR(@Date) end)

assuming @Date is your [Start_Date] field, you can:

假设@Date是您的[Start_Date]字段,您可以:

select * from yourTable 
group by convert(varchar(4), 
case when MONTH([Start_Date]) < @FiscalYearStartMonth 
then YEAR([Start_Date]) -1 
else YEAR([Start_Date]) end)

Hope this helps

希望这可以帮助

#9

This is what would look like in oracle to get current financial year. Enter the date for which you want to find fiscal year in place of sysdate

这是oracle在当前财政年度的样子。输入您要查找会计年度的日期以代替sysdate

 SELECT '01-APR-'
 ||TO_CHAR((add_months(**sysdate**,-3)),'YYYY') FIN_YEAR_START_DATE,
 '31-MAR-'
 ||(TO_CHAR((add_months(**sysdate**,-3)),'YYYY')+1) FIN_YEAR_END_DATE
 FROM dual;