找到最后一个季度的第一天和最后一天在ORACLE
我有以下形式的查询:找到最后一个季度的第一天和最后一天在ORACLE
select *
from X
where <some_date is in the last quarter>
我真的具有得到正确的日期,最后一个季度的麻烦。所以,说当前的日期是月,即在第三季度的第一次,我想获得四月作为FIRST的第1和第6月30日作为最后一个季度的最后一天(即第二季度)。
谷歌搜索了一下,发现了大量的解决方案,但是他们每个人都涵盖了SQL Server,并且在我们的ORACLE数据库(Oracle 10g和11g)上没有提供的那些可用的方法。
噢,而且我需要能够把整个事情变成一个查询,因为这是由一些工具把在我身上的限制,将进一步与此查询...工作:/
这一个更简单,但仍可能不是最简单的方法:
SELECT
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First,
TRUNC(SYSDATE, 'Q') - 1 AS Last
FROM DUAL
也许你也可以使用子查询,像这样,以排除一些代码重复:
SELECT
ADD_MONTHS(D, -3) AS First,
D - 1 AS Last
FROM (SELECT TRUNC(SYSDATE, 'Q') AS D FROM DUAL)
一般来说,只要我寻求帮助,我就会找到一些博客,让我知道如何继续下去。
管理敲了一些声明,但它是绝对的,但很丑陋。 :)
select
TRUNC(ADD_MONTHS(sysdate, -3),'Q') as first,
LAST_DAY(TRUNC(ADD_MONTHS(sysdate, -3),'Q')+ 85) as last
from dual;
这样做的伎俩,但如果有人知道更好的解决方案,请让我知道! (to_date('27-JAN-11')
在那里作为一个例子日期...)
编辑:修正了一个错误 - 增加3个月的一个季度的第一天并不总是在同一季度结束。现在它更丑陋 - 该死的是你的公历!
这是做的一种方式,这样可以节省不得不制定出第一天和最后一天的麻烦,并把结果的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)
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;
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;
SELECT
TRUNC(SYSDATE, 'Q')AS FIRST_DAY,
last_day(add_months(TRUNC(SYSDATE, 'Q'),2)) as LAST_DAY
FROM DUAL;
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 FRO中号 ( 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 )
请编辑您的文章,以便代码格式化。谢谢。 –