当前位置：首页 > article >正文

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

article 2025/1/18 9:18:14

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

1. MySQL生成日期表

1.1 日期格式：yyyy-MM-dd 字符型 2024-01-02

-- 生成日期表
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
;
/*
datekey
2023-01-01
2023-01-02
2023-01-03
2023-01-04
*/

1.2 日期格式：yyyyMMdd 数字型 20240102

-- 生成日期表
WITH RECURSIVE temp_dateTable AS (
  SELECT 20240101 AS datekey
  UNION ALL
  SELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), "%Y%m%d")+0
  FROM temp_dateTable
  WHERE datekey < 20240117
)
select
	dt.datekey
from temp_dateTable dt
;
/*
datekey
20240101
20240102
20240103
20240104
*/

1.3 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生成日期表

2.1 日期格式：yyyy-MM-dd 字符型 2024-01-02

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.2 日期格式：yyyyMMdd 数字型 20240102

DECLARE @BeginDate INT, @EndDate INT;
SET @BeginDate = 20240101; 
SET @EndDate = 20240117;   
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT format(dateadd(day, 1, cast(CalendarDate as varchar(8))), 'yyyyMMdd')+0
         FROM cteDate
         WHERE CalendarDate < @EndDate
)
select
	CalendarDate
from cteDate
/*
CalendarDate
20240101
20240102
20240103
20240104
*/

2.3 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] )
)

日期表生成