SQL 在 ORACLE 中查找最后一个季度的第一天和最后一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7348226/
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
Find First and Last Day of the last Quarter in ORACLE
提问by fgysin reinstate Monica
I have a query of the form:
我有一个表格查询：
select *
from X
where <some_date is in the last quarter>
I'm really having trouble with getting the correct dates for the last quarter. So, say current date is 1st of July, i.e. in the third quarter, I'd like to get the 1st of Aprilas FIRST and the 30th of Juneas the LAST day of the last quarter(i.e the second quarter).
我真的很难获得上个季度的正确日期。因此，假设当前日期是7 月 1 日，即在第三季度，我想将4 月 1 日作为 FIRST，将6 月 30 日作为最后一个季度（即第二季度）的最后一天。
Googled a bit and found tons of solutions on this, but each and every one of them covered SQL Server and the funky methods which are available there are not available on our ORACLE database (Oracle 10g and 11g).
用谷歌搜索了一下，找到了大量的解决方案，但每一个都涵盖了 SQL Server 和我们的 ORACLE 数据库（Oracle 10g 和 11g）上没有的可用的时髦方法。
Oh yeah, and also I need to be able to put the whole thing into one query, as that is a restriction put upon me by some tool that will further work with this query... :/
哦，是的，而且我还需要能够将整个事情放入一个查询中，因为这是某些工具对我施加的限制，该工具将进一步处理此查询...：/
回答by Andriy M
This one is simpler, but may still be not the simplest way:
这个更简单，但可能仍然不是最简单的方法：
SELECT
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First,
TRUNC(SYSDATE, 'Q') - 1 AS Last
FROM DUAL
Maybe you could also use a subselect, like this, to exclude some repetition of code:
也许你也可以像这样使用子选择来排除一些重复的代码：
SELECT
ADD_MONTHS(D, -3) AS First,
D - 1 AS Last
FROM (SELECT TRUNC(SYSDATE, 'Q') AS D FROM DUAL)
回答by Ganesh C
SELECT
TRUNC(SYSDATE, 'Q')AS FIRST_DAY,
last_day(add_months(TRUNC(SYSDATE, 'Q'),2)) as LAST_DAY
FROM DUAL;
回答by Garima
SELECT MIN (t), MAX (LAST_DAY (t))
FROM ( SELECT ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), LEVEL - 1) t,
TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'yyyy'), LEVEL - 1), 'Q')
r
FROM DUAL
CONNECT BY LEVEL <= 12) a
WHERE a.r = 4;
回答by MSolver
SELECT DATE_CURRENT
, TRUNC (ADD_MONTHS (DATE_CURRENT, -6), 'Q') AS FIRST
, LAST_DAY (TRUNC (ADD_MONTHS (DATE_CURRENT, -6), 'Q') + 85) AS LAST
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-1)) AS PREVIOUS_QUARTER_END
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-1)),-3)+1 AS PREVIOUS_QUARTER_START
FROM
(
SELECT TO_DATE ('31.07.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('30.06.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('30.04.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('31.03.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
)
order by DATUM;
回答by fgysin reinstate Monica
Typical, as soon as I ask for help I find some blog which gives me the right idea how to proceed.
典型的是，一旦我寻求帮助，我就会找到一些博客，这让我知道如何继续。
Managed to knock some statement together, however it is absolutetly but-f***ing ugly. :)
设法将一些声明放在一起，但它绝对是丑陋的。:)
select
TRUNC(ADD_MONTHS(sysdate, -3),'Q') as first,
LAST_DAY(TRUNC(ADD_MONTHS(sysdate, -3),'Q')+ 85) as last
from dual;
This does the trick as it seems, however if someone knows of a better solution, please let me know! (to_date('27-JAN-11')is in there as an example date...)
这看起来很有效，但是如果有人知道更好的解决方案，请告诉我！（to_date('27-JAN-11')在那里作为示例日期...）
Edit: Fixed a bug - adding 3 months to the first day of a quarter does not always end up in the same quarter. Now its even uglier - damn you gregorian calendar!
编辑：修复了一个错误 - 在一个季度的第一天添加 3 个月并不总是在同一季度结束。现在它更丑了——该死的公历！
回答by StevieG
This is one way to do it, it saves the bother of having to work out the first and last dates and put the results in a where clause for your main query:
这是一种方法，它省去了计算第一个和最后一个日期并将结果放在主查询的 where 子句中的麻烦：
select
*,
round(to_number(to_char(some_date, 'mm')) / 4) as quarter
from x
where round(to_number(to_char(some_date, 'mm')) / 4) = round(to_number(to_char(sysdate, 'mm')) / 4)
回答by Mahantesh Hiremath
SELECT DATE_CURRENT
, TRUNC (DATE_CURRENT, 'Q') AS Q1F
, LAST_DAY (ADD_MONTHS(TRUNC (DATE_CURRENT, 'Q'),2)) AS Q1L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-1)) AS Q2F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-1)),-3)+1 AS Q2L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-4)) AS Q3F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-4)),-3)+1 AS Q3L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-7)) AS Q4F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-7)),-3)+1 AS Q4L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-10)) AS Q5F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-10)),-3)+1 AS Q5L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-13)) AS Q6F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-13)),-3)+1 AS Q6L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-16)) AS Q7F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-16)),-3)+1 AS Q7L
, LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-19)) AS Q8F
, ADD_MONTHS(LAST_DAY (ADD_MONTHS(trunc(DATE_CURRENT,'Q'),-19)),-3)+1 AS Q8L
FROM
(
SELECT TO_DATE ('05.03.2017', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('30.06.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('30.04.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
UNION SELECT TO_DATE ('31.03.2014', 'DD.MM.YYYY') AS DATE_CURRENT FROM DUAL
)
