Like this:

Here’s a quick and easy way to generate a date dimension.

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
       dt date_value,
       TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
       TO_CHAR (dt, 'Mon DD, YYYY') date_name,
       TO_CHAR (dt, 'YYYY') year_num,
       TRUNC (dt, 'YYYY') year_start_date,
       ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
       TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
       'Q' || TO_CHAR (dt, 'Q') quarter_name,
       TRUNC (dt, 'Q') quarter_start_date,
       ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
       TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
       TO_CHAR (dt, 'MM') month_num,
       TO_CHAR (dt, 'Mon') month_short_name,
       TO_CHAR (dt, 'Month') month_long_name,
       TRUNC (dt, 'MM') month_start_date,
       LAST_DAY (dt) month_end_date,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
       TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
       TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
       TO_CHAR (dt, 'WW') week_of_year_num,
       TO_CHAR (dt, 'W') week_of_month_num,
       TRUNC (dt, 'W') week_start_date,
       TRUNC (dt, 'W') + 6 week_end_date,
       TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
       TO_CHAR (dt, 'D') weekday_num,
       TO_CHAR (dt, 'Day') weekday_name
  FROM (SELECT     TO_DATE (:start_date) + ROWNUM dt
              FROM DUAL
        CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

Advertisement