-- Query to get quarters of a year in Gregorian Calendar -- format using Oracle database functions. WITH q_year AS (SELECT TRUNC(TO_DATE(&i_yyyy,'yyyy'),'yyyy') first_day FROM DUAL), q_cal AS ( SELECT first_day, LAST_DAY(ADD_MONTHS(first_day,11)) last_day, LAST_DAY(ADD_MONTHS(first_day,11))+1 - first_day days_of_yr FROM q_year), q_ydys AS ( SELECT first_day+level-1 cal_day, LAST_DAY(first_day+level-1) last_day, to_number(to_char(first_day+level-1,'mm')) month_no --, days_of_yr FROM q_cal CONNECT BY level <= days_of_yr), q_all AS ( SELECT cal_day, last_day, month_no, DECODE(GREATEST(month_no,4), month_no, DECODE(GREATEST(month_no,7), month_no, DECODE(GREATEST(month_no,10), month_no,4,3),2),1) qtr FROM q_ydys) SELECT MIN(cal_day) qtr_start, MAX(cal_day) qtr_end, qtr FROM q_all GROUP BY qtr ORDER BY qtr ; => Gregorian Calendar - 2014 Quarters Output
22167