SQL 在sql select语句中计算财政年度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20554611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
calculate fiscal year in sql select statement?
提问by James123
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 语句中做到这一点？
回答by Gordon Linoff
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
回答by David
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
回答by CG3
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
回答by AMRENDRA PRASAD
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
回答by Thomas Pollack
...something simple :)
...一些简单的:)
(YEAR(DATEADD(Month,-((DATEPART(Month,[Date])+5) %12),[Date]))+) AS Financial_Year
(YEAR(DATEADD(Month,-((DATEPART(Month,[Date])+5) %12),[Date]))+) AS Financial_Year
回答by Stanley Tso
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
希望这可以帮助
回答by Ritanjan
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;
回答by Satish
For only year format (FY 2020) use following query:
对于仅年份格式（2020 财年），请使用以下查询：
SELECT tender_opening_date,CASE WHEN MONTH( order_date) >= 4
THEN CONCAT('FY ',YEAR(( order_date)) +1 )
ELSE CONCAT('FY ',YEAR( order_date)-1 )
END AS Fiscal_Year
FROM orders_tbl
For full financial Year (FY 2019-20) use following query:
对于完整的财政年度（FY 2019-20），请使用以下查询：
SELECT tender_opening_date,CASE WHEN MONTH( order_date) >= 4
THEN CONCAT('FY ',YEAR( order_date),'-',DATE_FORMAT( order_date,'%y') +1 )
ELSE CONCAT('FY ',YEAR( order_date)-1,'-',DATE_FORMAT( order_date,'%y') )
END AS Fiscal_Year
FROM orders_tbl
回答by Daniel B
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 per Year，并且您想查找给定日期 @Date 的财政年度：
SELECT MIN(Year) WHERE @Date >= Start_Date FROM Fiscal_Year
回答by KumarHarsh
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
