Oracle Sql First And Last Day Of Quarter Of Any Year

Is there any way i can calculate the first and last day of the three quarters in any year . 2012 , 2013 or 2014 SELECT ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First, TRUNC(SY

Solution 1:

You could do the following:

with q(qtr) as(
  select add_months(
                    DATE'2013-01-01'
                    , (level-1)*3
                    )
    from dual 
  connect by level <= 4
)
select qtr                          as first_day
     , last_day(add_months(qtr, 2)) as last_day
  from q

Result:

FIRST_DAY     LAST_DAY
-----------  -----------
01.01.2013    31.03.2013
01.04.2013    30.06.2013
01.07.2013    30.09.2013
01.10.2013    31.12.2013

SQLFIddle Demo

Solution 2:

This is one way of doing it

select  to_date('01-JAN-'||to_char(yr), 'DD-MON-YYYY') first_qtr,
   to_date('01-APR-'||to_char(yr), 'DD-MON-YYYY') second_qtr,
   to_date('01-JUL-'||to_char(yr), 'DD-MON-YYYY') third_qtr,
   to_date('01-OCT-'||to_char(yr), 'DD-MON-YYYY') fourth_qtr
   from ( select :year yr from dual )
UNIONALLselect to_date('01-APR-'||to_char(yr), 'DD-MON-YYYY')-1 first_qtr,
   to_date('01-JUL-'||to_char(yr), 'DD-MON-YYYY')-1 second_qtr,
   to_date('01-OCT-'||to_char(yr), 'DD-MON-YYYY')-1 third_qtr,
   to_date('01-JAN-'||to_char(yr+1), 'DD-MON-YYYY')-1 fourth_qtr
   from ( select :year yr from dual )

I have used bind variables so change it to your requirements accordingly.

I am fairly new to Oracle, so other's can give a simplified code.

The output when given 2009 would be as below

FIRST_QTR   SECOND_QTR  THIRD_QTR   FOURTH_QTR
01/01/2009  04/01/2009  07/01/2009  10/01/2009
03/31/2009  06/30/2009  09/30/2009  12/31/2009

Solution 3:

This is an old question but maybe this will be helpful:

WITH y1 AS (
    SELECT LEVEL + 2000AS the_year
      FROM dual
   CONNECT BY LEVEL <= 20
), q1 AS (
    SELECT LEVEL AS the_quarter
      FROM dual
    CONNECT BY LEVEL <= 4
)
SELECT the_year, the_quarter
     , TO_CHAR(first_day, 'DAY') AS first_dw, first_day
     , TO_CHAR(last_day, 'DAY') AS last_dw, last_dayFROM (
    SELECT the_year, the_quarter
         , ADD_MONTHS(TO_DATE(the_year, 'YYYY'), 3 * (the_quarter - 1)) AS first_day
         , ADD_MONTHS(TO_DATE(the_year, 'YYYY'), 3 * the_quarter) - 1 AS last_dayFROM y1, q1
)

Solution 4:

One line per year, each line consisting of the year plus 8 (=2 dates per quarter) dates:

with params as (
  select2012as start_year,
    2014as end_year
  from
    dual
)
select
           start_year+ level -1year,
  to_date((start_year+ level -1) ||'0101', 'yyyymmdd')  start_q1,
  to_date((start_year+ level -1) ||'0331', 'yyyymmdd')  end_q1  ,
  to_date((start_year+ level -1) ||'0401', 'yyyymmdd')  start_q2,
  to_date((start_year+ level -1) ||'0630', 'yyyymmdd')  end_q2  ,
  to_date((start_year+ level -1) ||'0701', 'yyyymmdd')  start_q3,
  to_date((start_year+ level -1) ||'0930', 'yyyymmdd')  end_q3  ,
  to_date((start_year+ level -1) ||'1001', 'yyyymmdd')  start_q4,
  to_date((start_year+ level -1) ||'1231', 'yyyymmdd')  end_q4
from
  dual, params
connectby
  start_year + level -1<= end_year;