How to build a calendar DIM_DATE table in Snowflake without using any source table

The calendar table is used extensively in reporting to generate weekly / monthly /quarterly reports. To build a calendar table, you don't have to start from scratch, you can use the below query to build a Calendar table in Snowflake.

Query:

CREATE OR REPLACE TABLE MY_DATE_DIMENSION (
   MY_DATE          DATE        NOT NULL
  ,YEAR             SMALLINT    NOT NULL
  ,MONTH            SMALLINT    NOT NULL
  ,MONTH_NAME       CHAR(3)     NOT NULL
  ,DAY_OF_MON       SMALLINT    NOT NULL
  ,DAY_OF_WEEK      VARCHAR(9)  NOT NULL
  ,WEEK_OF_YEAR     SMALLINT    NOT NULL
  ,DAY_OF_YEAR      SMALLINT    NOT NULL
)
AS
  WITH CTE_MY_DATE AS (
    SELECT DATEADD(DAY, SEQ4(), '2000-01-01') AS MY_DATE
      FROM TABLE(GENERATOR(ROWCOUNT=>10000))  
-- Number of days after reference date in previous line
  )
  SELECT MY_DATE
        ,YEAR(MY_DATE)
        ,MONTH(MY_DATE)
        ,MONTHNAME(MY_DATE)
        ,DAY(MY_DATE)
        ,DAYOFWEEK(MY_DATE)
        ,WEEKOFYEAR(MY_DATE)
        ,DAYOFYEAR(MY_DATE)
    FROM CTE_MY_DATE
;

dan-irving

posted on 30 Oct 18