-- 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
55702