从 oracle 中的某个日期获取财政年度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17761392/
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
get financial year from a date in oracle
提问by abhi
Is there a way to find out the current financial year (2013-2014) from a date (24/07/2013) in a short way.
有没有办法从一个日期 (24/07/2013) 以简短的方式找出当前财政年度 (2013-2014)。
I have tried this
我试过这个
select extract(year from report_date)||' - '||
extract(year from report_date) + 1 year
from bdpr_swtch_yrd
and apart from this if I can find out, in which quarter the selected date belongs.
除此之外，如果我能找出所选日期属于哪个季度。
回答by Noel
Since there is a offset of 3 months, you should subtract 3 months from current date and then extract the year.
由于有 3 个月的偏移量，您应该从当前日期减去 3 个月，然后提取年份。
SELECT EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -3))
|| '-'
|| EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, 9))
FROM DUAL;
Output:2013-2014
输出：2013-2014
SELECT EXTRACT (YEAR FROM ADD_MONTHS (DATE '2013-03-31', -3))
|| '-'
|| EXTRACT (YEAR FROM ADD_MONTHS (DATE '2013-03-31', 9))
FROM DUAL;
Output:2012-2013
输出：2012-2013
EDIT:
编辑：
To find all records in the financial year, first you need to find the start and end date of the financial year, which is always 1st April and 31st March. There are several ways to get this. I'll continue with the same approach as above, since we already have got the year.
要查找财政年度中的所有记录，首先您需要找到财政年度的开始和结束日期，始终是 4 月 1 日和 3 月 31 日。有几种方法可以得到这个。我将继续采用与上述相同的方法，因为我们已经有了这一年。
Start_date:= to_date('01-04-'||EXTRACT (YEAR FROM ADD_MONTHS (reference_date, -3)),'dd-mm-yyyy')
End_date := to_date('01-04-'||EXTRACT (YEAR FROM ADD_MONTHS (reference_date, 9)),'dd-mm-yyyy')
So, your where clause should look something like this.
所以，你的 where 子句应该看起来像这样。
select * from table_name
where column_name between Start_date and End_date;
回答by ROhit
SELECT CASE
WHEN TO_CHAR (indate, 'MMdd') > '0331'
THEN to_char(indate,'yyyy')
ELSE to_char(to_number(to_char(indate,'yyyy'))-1)
END AS FY
FROM DUAL;
For other formats:
对于其他格式：
select FY||'-'||(FY+1) as FY,FY||'-'||substr((FY+1),3,2) as FYN from (
SELECT CASE
WHEN TO_CHAR (sysdate, 'MMdd') > '0331'
THEN to_char(sysdate,'yyyy')
ELSE to_char(to_number(to_char(sysdate,'yyyy'))-1)
END AS FY
FROM DUAL);
回答by ATUL
SELECT SYSDATE,CASE
WHEN TO_CHAR (SYSDATE, 'MMdd') > '0331'
THEN to_char(SYSDATE,'yyyy')||'-'||to_char(to_number(to_char(SYSDATE,'yyyy'))+1)
ELSE to_char(to_number(to_char(SYSDATE,'yyyy'))-1)||'-'||to_char(SYSDATE,'yyyy')
-- to_char(to_number(to_char(SYSDATE,'yyyy'))-1)
END AS FY
FROM DUAL;
Try this, Hope this helps
试试这个，希望这有帮助
回答by Mark Rogers
For many, the fiscal year begins in october, which puts the offset at +3. Borrowing from the code from Noel:
对于许多人来说，财政年度从 10 月开始，抵消额为 +3。借用 Noel 的代码：
extract( year from add_months(date '2013-10-15',3)) or extract( year from add_months(sysdate,3))
提取（年份来自 add_months(date '2013-10-15',3)) 或提取（年份来自 add_months(sysdate,3))
This code gives the fiscal year for any given date. you can then use a +1 or -1 year or duplicate the code to do specifically what you're looking for.
此代码给出任何给定日期的财政年度。然后，您可以使用 +1 或 -1 年或复制代码来专门执行您要查找的操作。
回答by mti2935
This should return the fiscal year based on the current date, given that your fiscal year ends March 31:
鉴于您的财政年度于 3 月 31 日结束，这应该返回基于当前日期的财政年度：
SELECT
EXTRACT(YEAR FROM sysdate) + DECODE(SIGN(TO_CHAR(sysdate, 'MM') - 3), -1, 0, 1)
AS FISCAL_YEAR
FROM DUAL
This should return the quarter of the current date:
这应该返回当前日期的季度：
SELECT to_char(sysdate, 'Q')
FROM DUAL
