oracle 将日期截断到会计年度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2678869/
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
Truncate date to fiscal year
提问by Dave Jarvis
The following database view truncates the date to the fiscal year (April 1st):
以下数据库视图将日期截断为会计年度（4 月 1 日）：
CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
SELECT
CASE
WHEN to_number(to_char(SYSDATE, 'MM')) < 4 THEN
to_date('1-APR-'||to_char(add_months(SYSDATE, -12), 'YYYY'), 'dd-MON-yyyy')
ELSE
to_date('1-APR-'||to_char(SYSDATE, 'YYYY'), 'dd-MON-yyyy')
END AS fiscal_year
FROM
dual;
This allows us to calculate the current fiscal year based on today's date.
这使我们能够根据今天的日期计算当前的会计年度。
How can this calculation be simplified or optimized?
如何简化或优化此计算？
回答by Jeffrey Kemp
ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE,-3),'YYYY'),3)
回答by Indolent Coder
Perhaps this...
或许这...
SELECT to_date('01/04/' ||
to_char(extract(YEAR FROM SYSDATE)
- CASE WHEN extract(MONTH FROM SYSDATE) BETWEEN 1 AND 4 THEN 1 ELSE 0 END),
'DD/MM/YYYY') FROM dual;
I guess this is another option...
我想这是另一种选择......
SELECT add_months(trunc(SYSDATE) - extract(DAY FROM SYSDATE) + 1,
- (extract(MONTH FROM SYSDATE) + CASE
WHEN extract(MONTH FROM SYSDATE) <= 4 THEN 12 ELSE 0 END) + 4)
FROM dual;
Other options are rewriting as a function that returns a date, or the logic could be simplified if you could just return the year number for the current fiscal year since you'd only need the logic within the to_char.
其他选项是重写为返回日期的函数，或者如果您可以只返回当前财政年度的年份编号，则可以简化逻辑，因为您只需要 to_char 中的逻辑。
回答by APC
TRUNC() can be usefully applied to dates, with different format masks. Most pertinently, trunc(sysdate, 'yyyy')gives us the first day of the year. So this will give us the 01-APR of the current year ...
TRUNC() 可以有用地应用于具有不同格式掩码的日期。最相关的是，trunc(sysdate, 'yyyy')给了我们一年的第一天。所以这会给我们今年的 01-APR ......
add_months(trunc(sysdate, 'yyyy'), 3)
and this that date for the previous year ...
这是前一年的那个日期......
add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3)
So:
所以：
CREATE OR REPLACE VIEW FISCAL_YEAR_VW AS
WITH cte as
( select add_months(trunc(sysdate, 'yyyy'), 3) as this_year
, add_months(trunc(add_months(sysdate, -12), 'yyyy'), 3) as last_year
from dual )
SELECT
CASE
WHEN SYSDATE >= cte.this_year THEN
cte.this_year
ELSE
cte.last_year
END AS fiscal_year
FROM
cte;
caveat: I haven't had the chance to test this code yet so it might contain typos. I will test it later and correct it if necessary.
警告：我还没有机会测试这段代码，所以它可能包含拼写错误。稍后我将对其进行测试并在必要时进行更正。
回答by Brian
I find the TO_CHAR(date, 'Q') feature of oracle very useful for calculating fiscal calendars. The query below uses the 'with' clause to build two things
我发现 oracle 的 TO_CHAR(date, 'Q') 功能对于计算财政日历非常有用。下面的查询使用“with”子句来构建两件事
- Sample Data- test_dates table.
- fiscal_map- a simple mapping of the calendar quarters to your fiscal calendar. In this example the 4th calendar quarter is the 1st fiscal quarter (October 1st).
- 示例数据- test_dates 表。
- 财政地图- 日历季度到您的财政日历的简单映射。在此示例中，第 4 个日历季度是第 1 个会计季度（10 月 1 日）。
Example:
例子：
with test_dates as (
select sysdate + level * 80 test_date from dual connect by level < 11
),
fiscal_map as (
select 1 cal, 2 fiscal from dual
union
select 2 cal, 3 fiscal from dual
union
select 3 cal, 4 fiscal from dual
union
select 4 cal, 1 fiscal from dual
)
select
test_date,
TO_CHAR(test_date, 'Q') cal_quarter,
fiscal_map.fiscal,
(case when CAL < fiscal then
TO_CHAR(test_date, 'yyyy') + 0
else TO_CHAR(test_date, 'yyyy') + 1
end) FISCAL_YEAR
from test_dates, fiscal_map
where fiscal_map.cal = TO_CHAR(test_date, 'Q')
order by test_date
Output:
输出：
TEST_DT CAL_Q FISCAL Q FISCAL_YR
22-Jul-10 3 4 2010
10-Oct-10 4 1 2011
29-Dec-10 4 1 2011
19-Mar-11 1 2 2011
07-Jun-11 2 3 2011
26-Aug-11 3 4 2011
14-Nov-11 4 1 2012
02-Feb-12 1 2 2012
22-Apr-12 2 3 2012
11-Jul-12 3 4 2012
回答by Hemant
select T.USERNAME,T.CREATED,
CASE WHEN EXTRACT (MONTH FROM T.CREATED)>=4 AND EXTRACT (MONTH FROM T.CREATED)<=12 THEN
TO_CHAR(EXTRACT (YEAR FROM T.CREATED))||'-'||TO_CHAR(EXTRACT (YEAR FROM T.CREATED)+1)
WHEN EXTRACT (MONTH FROM T.CREATED)<4 THEN
TO_CHAR(EXTRACT (YEAR FROM T.CREATED)-1)||'-'||TO_CHAR(EXTRACT (YEAR FROM T.CREATED)) ELSE NULL END FY
from sys.dba_users t WHERE T.USERNAME in ('101655','100149')
Output will be:
输出将是：
1 101655 14/01/2014 12:21:53 2013-2014
2 100149 05/05/2012 16:55:00 2012-2013
