Calendar Data - Bi-Yearly or Semi-Annual
-- This is a query to create semi-annual or bi-yearly calendar dates. The reference 
-- date can be changed as shown below to use the Oracle SYSDATE to start from 
-- begining of current year as reference.

WITH q_base AS
 (SELECT  &yyyy  as ref_year    -- TRUNC(SYSDATE,'YYYY')
   FROM  DUAL),
q_date AS
 (SELECT  ref_year,  TO_DATE(ref_year,'yyyy') ref_date,  6 mth_cnt
  FROM q_base),
q_cal AS
 (SELECT 
    TO_CHAR(ADD_MONTHS(TRUNC(ref_date,'yyyy'),(level-1)*mth_cnt),'yyyy') ref_year, 
    ADD_MONTHS(TRUNC(ref_date,'yyyy'),(level-1)*mth_cnt) start_date,
    ADD_MONTHS(TRUNC(ref_date,'yyyy'),level*mth_cnt)-1 end_date
  FROM q_date
  CONNECT BY level <= 16)
SELECT
 ref_year,
 ROW_NUMBER() OVER (PARTITION BY ref_year ORDER BY start_date) yr_period,
 start_date,  end_date,
 end_date+1-start_date period_days
FROM q_cal;

=> Above Query Output

Reference:

[1] Weekly Date Query

[2] Quarterly Date Query

[3] Row_Number() and RANK() Query

Oracle registered trademark of Oracle Corporation.

Last Revised On: August 9th, 2014

  394