How can I create a distinct list of dates for my date field in a separate sql view and then add calculated columns to that date field to extract the year, month, month name, and possibly the day.

So below is what I want my sql view to return

DATE        |  DAY  |  MONTH  |  MONTH_NAME  |  YEAR
------------------------------------------------
01-01-2020  |    01 |      01 | January      |  2020
03-01-2020  |    03 |      01 | January      |  2020
ETC....

CodePudding user response：

Use virtual columns:

CREATE TABLE calendar (
  dt         DATE PRIMARY KEY,
  day        NUMBER(2,0) GENERATED ALWAYS AS (EXTRACT(DAY FROM dt)),
  month      NUMBER(2,0) GENERATED ALWAYS AS (EXTRACT(MONTH FROM dt)),
  month_name VARCHAR2(9)
             GENERATED ALWAYS AS (
               CAST(
                 TO_CHAR(dt, 'fmMonth', 'NLS_DATE_LANGUAGE=ENGLISH')
                 AS VARCHAR2(9)
               )
             ),
  year       NUMBER(4,0) GENERATED ALWAYS AS (EXTRACT(YEAR FROM dt))
);

Then:

INSERT INTO calendar (dt)
SELECT DATE '2020-01-01' FROM DUAL UNION ALL
SELECT DATE '2020-01-03' FROM DUAL;

The output of:

SELECT * FROM calendar;

Is:

DT	DAY	MONTH	MONTH_NAME	YEAR
2020-01-01 00:00:00	1	1	January	2020
2020-01-03 00:00:00	3	1	January	2020

If you want to create a view of an existing table (for example, a view of the calendar table) then just use the same code in the SELECT of the view as in the virtual columns above:

CREATE VIEW calendar_view (dt, day, month, month_name, year) AS
SELECT dt,
       EXTRACT(DAY FROM dt),
       EXTRACT(MONTH FROM dt),
       TO_CHAR(dt, 'fmMonth', 'NLS_DATE_LANGUAGE=ENGLISH'),
       EXTRACT(YEAR FROM dt)
FROM   calendar;

Note: You can add the CAST(... AS VARCHAR2(9)) if you want to restrict the size of the string but it is not necessary.

db<>fiddle here