calculate fiscal year in sql select statement?

问题:

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?

回答1:

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.

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

回答2:

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

回答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.

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:

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

回答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:

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

 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;