-- 创建存储过程，向MYTABLE表插入日期数据
CREATE OR REPLACE PROCEDURE FILL_MYTABLE AS
BEGIN
  FOR YEAR_IN_NUMBER IN 2000..2100 LOOP
    FOR MONTH_IN_NUMBER IN 1..12 LOOP
      FOR QUARTER_IN_NUMBER IN 1..4 LOOP
        FOR DAY_IN_NUMBER IN 1..31 LOOP
          -- 计算日期
          DECLARE
            CURRENT_DATE1 DATE;
            CURRENT_QUARTER VARCHAR2(4);
            CURRENT_MONTH NUMBER;
            CURRENT_WEEKDAY VARCHAR2(2);
          BEGIN
            CURRENT_DATE1 := TO_DATE(YEAR_IN_NUMBER || '-' || LPAD(MONTH_IN_NUMBER, 2, '0') || '-' || LPAD(DAY_IN_NUMBER, 2, '0'), 'YYYY-MM-DD');
            CURRENT_QUARTER := 'Q' || QUARTER_IN_NUMBER;
            CURRENT_MONTH := MONTH_IN_NUMBER;
            -- 计算星期几
            CURRENT_WEEKDAY := CASE WHEN TO_CHAR(CURRENT_DATE1, 'D') BETWEEN 2 AND 6 THEN 'YES' ELSE 'NO' END;
            
            -- 插入数据到MYTABLE表
            INSERT INTO MYTABLE (ID, DATE1, QUARTAL, MONTH, WEEKDAY) VALUES (TO_CHAR(CURRENT_DATE1, 'YYYY'), CURRENT_DATE1, CURRENT_QUARTER, CURRENT_MONTH, CURRENT_WEEKDAY);
          END;
        END LOOP;
      END LOOP;
    END LOOP;
  END LOOP;
END FILL_MYTABLE;
/

SQL> alter session set nls_date_Format = 'yyyy-mm-dd';
Session altered.

SQL> with temp (datum) as
  2    (select date '2000-01-01' + level - 1
  3     from dual
  4     connect by level <= date '2100-01-01' - date '2000-01-01' + 1
  5    )
  6  select min(datum), max(datum) from temp;
MIN(DATUM) MAX(DATUM)
---------- ----------
2000-01-01 2100-01-01

SQL> with temp (datum) as
  2    (select date '2000-01-01' + level - 1
  3     from dual
  4     connect by level <= date '2100-01-01' - date '2000-01-01' + 1
  5    )
  6  select extract(year from datum) as id,
  7    datum as date1,
  8    to_char(datum, 'q') as quartal,
  9    extract(month from datum) as month,
 10    case when to_char(datum, 'dy', 'nls_date_language = english') in ('sat', 'sun') then 'N'
 11         else 'Y'
 12    end as weekday
 13  from temp
 14  where datum between date '2000-03-25' and date '2000-04-05'
 15  order by datum;
        ID DATE1      Q      MONTH W
---------- ---------- - ---------- -
      2000 2000-03-25 1          3 N
      2000 2000-03-26 1          3 N
      2000 2000-03-27 1          3 Y
      2000 2000-03-28 1          3 Y
      2000 2000-03-29 1          3 Y
      2000 2000-03-30 1          3 Y
      2000 2000-03-31 1          3 Y
      2000 2000-04-01 2          4 N
      2000 2000-04-02 2          4 N
      2000 2000-04-03 2          4 Y
      2000 2000-04-04 2          4 Y
      2000 2000-04-05 2          4 Y
12 rows selected.
SQL>

--
-- 创建日历表
--
-- 如果已经存在，删除表
--
--DROP TABLE CALENDAR_TABLE;
--
CREATE TABLE CALENDAR_TABLE AS
	WITH 
		base_calendar AS
			(
				SELECT CurrDate         AS Day_ID,
				1                       AS Day_Time_Span,
				CurrDate                AS Day_End_Date,
				TO_CHAR(CurrDate,'Day') AS Week_Day_Full,
				TO_CHAR(CurrDate,'DY')  AS Week_Day_Short,
				TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'D')))                 AS Day_Num_of_Week,
				TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DD')))                AS Day_Num_of_Month,
				TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'DDD')))               AS Day_Num_of_Year,
				UPPER(TO_CHAR(CurrDate,'Mon') || '-' || TO_CHAR(CurrDate,'YYYY'))      AS Month_ID,
				TO_CHAR(CurrDate,'Mon') || ' ' || TO_CHAR(CurrDate,'YYYY')             AS Month_Short_Desc,
				RTRIM(TO_CHAR(CurrDate,'Month')) || ' ' || TO_CHAR(CurrDate,'YYYY')    AS Month_Long_Desc,
				TO_CHAR(CurrDate,'Mon')    AS Month_Short,
				TO_CHAR(CurrDate,'Month')  AS Month_Long,
				TO_NUMBER(TRIM(leading '0' FROM TO_CHAR(CurrDate,'MM')))                AS Month_Num_of_Year,
				'Q' || UPPER(TO_CHAR(CurrDate,'Q') || '-' || TO_CHAR(CurrDate,'YYYY')) AS Quarter_ID,
				TO_NUMBER(TO_CHAR(CurrDate,'Q'))                                       AS Quarter_Num_of_Year,
				CASE
				  WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
				  THEN 1
				  ELSE 2
				END                                                                      AS half_num_of_year,
				CASE
				  WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2
				  THEN 'H'
					|| 1
					|| '-'
					|| TO_CHAR(CurrDate,'YYYY')
				  ELSE 'H'
					|| 2
					|| '-'
					|| TO_CHAR(CurrDate,'YYYY')
				END                                                                      AS half_of_year_id,
				TO_CHAR(CurrDate,'YYYY')                                               AS Year_ID
			  FROM
				(
					SELECT level n,
						-- 日历从下一行中日期的后一天开始
						TO_DATE('31/12/1999','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate
					FROM dual
						-- 要添加到表中的天数在下一行中
					CONNECT BY level <= 36525
				)
			)
SELECT  day_id,
        day_time_span,
        day_end_date,
        week_day_full,
        week_day_short,
        day_num_of_week,
        day_num_of_month,
        day_num_of_year,
        month_id,
        COUNT(*) OVER (PARTITION BY month_id)    AS Month_Time_Span,
        MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,
        month_short_desc,
        month_long_desc,
        month_short,
        month_long,
        month_num_of_year,
        quarter_id,
        COUNT(*) OVER (PARTITION BY quarter_id)    AS Quarter_Time_Span,
        MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,
        quarter_num_of_year,
        half_num_of_year,
        half_of_year_id,
        COUNT(*) OVER (PARTITION BY half_of_year_id)    AS Half_Year_Time_Span,
        MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,
        year_id,
        COUNT(*) OVER (PARTITION BY year_id)    AS Year_Time_Span,
        MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date
FROM base_calendar
ORDER BY day_id

Select Min(DAY_ID) "START_DATE", Max(DAY_ID) "END_DATE", Count(DAY_ID) "NUM_OF_DATES" 
From Calendar_Table
START_DATE END_DATE  NUM_OF_DATES
---------- --------- ------------
01-JAN-00  31-DEC-99        36525