SQL和MySQL以及DAX的日期表生成？将生成的日期表插入到临时表或者实体表中

几种生成日期表的方法
如何用SQL语句生成日期表呢？
如何用MySQL语句生成日期表呢？
如何用DAX语句生成日期表呢？

1. MySQL生成日期表

-- 生成日期表
WITH RECURSIVE temp_dateTable AS (
  SELECT '2023-01-01' AS datekey
  UNION ALL
  SELECT DATE_ADD(datekey, INTERVAL 1 DAY)
  FROM temp_dateTable
  WHERE datekey < '2023-12-31'
)
SELECT datekey FROM temp_dateTable

1.2 MySQL生成的日期表插入到实体表（临时表）

-- 删除表
DROP TABLE IF EXISTS create_dateTable;

-- 创建表
CREATE TABLE create_dateTable (
    datekey DATE PRIMARY KEY
);

-- 将查找出来的临时表插入到新创建的实体表
INSERT INTO create_dateTable
WITH RECURSIVE temp_dateTable AS (
  SELECT '2023-01-01' AS datekey
  UNION ALL
  SELECT DATE_ADD(datekey, INTERVAL 1 DAY)
  FROM temp_dateTable
  WHERE datekey < '2023-12-31'
)
SELECT datekey FROM temp_dateTable;
-- 查找日期
SELECT * FROM create_dateTable;

2. SQL生成日期表

DECLARE @BeginDate DATE, @EndDate DATE;
SET @BeginDate = '2023-09-01'
SET @EndDate = '2023-12-01'
;
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT DATEADD(DAY,1,CalendarDate)
         FROM cteDate
         WHERE CalendarDate <= @EndDate
)
SELECT
        CalendarDate DateKey,
        (DATEPART(YEAR,CalendarDate) * 10000) + (DATEPART(MONTH,CalendarDate) * 100) +
        DATEPART(DAY,CalendarDate) CalendarDate,
        DATEPART(YEAR,CalendarDate) AS Year,
        DATEPART(MONTH,CalendarDate) AS MonthNumber,
        DATENAME(MONTH,CalendarDate) AS Month,
        DATENAME(QUARTER,CalendarDate) AS Quarter
        , format(CalendarDate ,'yyyyMM') YeamMonth
FROM cteDate
OPTION (MAXRECURSION 0)

2.1 SQL生成的日期表插入到实体表（临时表）

DECLARE @BeginDate DATE, @EndDate DATE;
SET @BeginDate = '2023-09-01'
SET @EndDate = '2023-12-01'
;
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT DATEADD(DAY,1,CalendarDate)
         FROM cteDate
         WHERE CalendarDate <= @EndDate
)
SELECT
        CalendarDate DateKey,
        (DATEPART(YEAR,CalendarDate) * 10000) + (DATEPART(MONTH,CalendarDate) * 100) +
        DATEPART(DAY,CalendarDate) CalendarDate,
        DATEPART(YEAR,CalendarDate) AS Year,
        DATEPART(MONTH,CalendarDate) AS MonthNumber,
        DATENAME(MONTH,CalendarDate) AS Month,
        DATENAME(QUARTER,CalendarDate) AS Quarter
        , format(CalendarDate ,'yyyyMM') YeamMonth
        INTO #temp_dateTable
FROM cteDate
OPTION (MAXRECURSION 0);
SELECT * FROM #temp_dateTable

3. DAX日期表生成 自动日期表

EVALUATE
ADDCOLUMNS (
     CALENDARAUTO(),
     "Year", YEAR ( [Date] ),
     "Quarter No", QUARTER ( [Date] ),
     "Quarter", "Q" & QUARTER ( [Date] ),
     "Month No", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "MM" ),
     "Day", DAY ( [Date] )
)

3.2 DAX日期表生成 指定日期表

EVALUATE
ADDCOLUMNS (
     CALENDAR ( DATE ( 2023, 11, 01 ), DATE ( 2023, 12, 31 ) ),
     "Year", YEAR ( [Date] ),
     "Quarter No", QUARTER ( [Date] ),
     "Quarter", "Q" & QUARTER ( [Date] ),
     "Month No", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "MM" ),
     "Day", DAY ( [Date] )
)