Find First and Last Day of the last Quarter in ORACLE

Question

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

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

Answer 1

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)

Answer 2

SELECT
  TRUNC(SYSDATE, 'Q')AS FIRST_DAY,
  last_day(add_months(TRUNC(SYSDATE, 'Q'),2)) as LAST_DAY                
FROM DUAL;

Answer 3

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;

Answer 4

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:

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)

Answer 5

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

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!

Answer 6

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;

Answer 7

    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
           )

Answer 8

i know that the topic is old and closed but that is the way i properly resolved the question.

SELECT TRUNC(to_date('05.06.2020', 'dd.mm.yyyy'), 'Q') f_date,
      LAST_DAY(ADD_MONTHS(TRUNC(TO_DATE('05.06.2020', 'dd.mm.yyyy'), 'Q'),2) ) l_date
 FROM dual;