查找ORACLE最后一个季度的第一天和最后一天 [英] Find First and Last Day of the last Quarter in ORACLE

问题描述

我有一个格式的查询：

select *
from X
where <some_date is in the last quarter>

我在最后一个季度获得正确的日期真的很困难。所以说现在的日期是7月1日，即在第三季度，我想把4月1日的 作为FIRST和6月30日作为上个季度的最后一天（即第二季度）。

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 April as FIRST and the 30th of June as the LAST day of the last quarter (i.e the second quarter).

Google搜索了一大堆解决方案这个，但是它们中的每一个都涵盖了SQL Server，而且我们的ORACLE数据库（Oracle 10g和11g）上并没有可用的时髦方法。

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).

哦是的，我也需要把整个事情放在一个查询中，因为这是一个可以进一步处理这个查询的工具给我的限制...：/

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... :/

推荐答案

这个更简单，但仍然不是最简单的方法：

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)

这篇关于查找ORACLE最后一个季度的第一天和最后一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！