SQLite Calendar Table

sqlite-calendar-table.sql

--DROP TABLE IF EXISTS calendar;
--CREATE TABLE calendar AS
WITH RECURSIVE dates_cte (dt) AS (
  VALUES('1970-01-01') -- Choose a start date
  UNION ALL
  SELECT date(dt, '+1 day')
  FROM dates_cte
  WHERE dt < '2050-12-31' -- Choose an end date
),
calendar_cte AS (
  SELECT    dt
          , CAST(strftime('%Y', dt) AS INT) AS year
          , CAST(strftime('%m', dt) AS INT) AS month
          , CAST(strftime('%d', dt) AS INT) AS day
          , CASE
              WHEN CAST(strftime('%m', dt) AS INT) BETWEEN 1 AND 3 THEN 1
              WHEN CAST(strftime('%m', dt) AS INT) BETWEEN 4 AND 6 THEN 2
              WHEN CAST(strftime('%m', dt) AS INT) BETWEEN 7 AND 9 THEN 3
              ELSE 4
            END AS quarter
	        , CAST(strftime('%W', dt) AS INT) + 1 AS week
          , (CAST(strftime('%w', dt) AS INT) + 6) % 7 + 1 AS dayofweek
          , CASE (CAST(strftime('%w', dt) AS INT) + 6) % 7
              WHEN 0 THEN 'Monday'
              WHEN 1 THEN 'Tuesday'
              WHEN 2 THEN 'Wednesday'
              WHEN 3 THEN 'Thursday'
              WHEN 4 THEN 'Friday'
              WHEN 5 THEN 'Saturday'
              ELSE 'Sunday'
            END AS weekday
  FROM dates_cte
)
SELECT    *
FROM      calendar_cte;