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

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

news 2025/7/22 1:16:44

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

1. MySQL生成日期表

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

-- 生成日期表
WITH RECURSIVE temp_dateTable AS (SELECT '2023-01-01' AS datekeyUNION ALLSELECT DATE_ADD(datekey, INTERVAL 1 DAY)FROM temp_dateTableWHERE 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 datekeyUNION ALLSELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), "%Y%m%d")+0FROM temp_dateTableWHERE datekey < 20240117
)
selectdt.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 datekeyUNION ALLSELECT DATE_ADD(datekey, INTERVAL 1 DAY)FROM temp_dateTableWHERE 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 CalendarDateUNION ALLSELECT DATEADD(DAY,1,CalendarDate)FROM cteDateWHERE CalendarDate <= @EndDate
)
SELECTCalendarDate 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 CalendarDateUNION ALLSELECT format(dateadd(day, 1, cast(CalendarDate as varchar(8))), 'yyyyMMdd')+0FROM cteDateWHERE CalendarDate < @EndDate
)
selectCalendarDate
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 CalendarDateUNION ALLSELECT DATEADD(DAY,1,CalendarDate)FROM cteDateWHERE CalendarDate <= @EndDate
)
SELECTCalendarDate 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') YeamMonthINTO #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] )
)

日期表生成