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;