fgysin fgysin - 3 months ago 10
SQL Question

Find First and Last Day of the last Quarter in ORACLE

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

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