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

Nisan

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)

Keenankeene

Answer 2

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

Kassab

Answer 3

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;

Fen

Answer 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;

Scrim

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!

Nisan

Answer 6

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)

Besse

Answer 7

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;

Markup

Answer 8

    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
           )

Anatase

Please edit your post so that the code is formatted. Thank you. – Estimative

Answer 9

SELECT TO_CHAR(sysdate, 'Q') AS QUARTER FROM DUAL
          
returns the Quarter Number (i.e. CHAR of either 1,2,3 or 4 )

Sexcentenary

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Burgwell