SQL server生成日期维度表（chatGPT）

阿云会加油的

于 2023-09-20 11:01:45 发布

阅读量363

点赞数

文章标签： sql sqlserver

本文链接：https://blog.csdn.net/buzai5/article/details/133070504

版权

CREATE PROCEDURE GenerateFutureDates
AS
BEGIN
    DECLARE @StartDate DATE = '2024-01-01';
    DECLARE @EndDate DATE = '2026-12-31';

    WHILE @StartDate <= @EndDate
    BEGIN
        INSERT INTO YourDateDimensionTable (Date, Year, YearName, Quarter, QuarterName, Month, MonthName, HBMonthName, TBMonthName, DayName, WeekName, Week, WeekDay, YearFirstDate, YearLastDate, QuarterFirstDate, QuarterLastDate, MonthFirstDate, MonthLastDate)
        VALUES (
            @StartDate,
            YEAR(@StartDate),
            CAST(YEAR(@StartDate) AS NVARCHAR(4)) + N'年',
            CONCAT(YEAR(@StartDate), 'Q', DATEPART(QUARTER, @StartDate)),
            CAST(YEAR(@StartDate) AS NVARCHAR(4)) + N'年' + CAST(DATEPART(QUARTER, @StartDate) AS NVARCHAR(1)) + N'季度',
            FORMAT(@StartDate, 'yyyyMM'),
            FORMAT(@StartDate, 'yyyy年MM月'),
            FORMAT(@StartDate, 'yyyy年MM月'),
            FORMAT(DATEADD(MONTH, 12, @StartDate), 'yyyy年MM月'),
            FORMAT(@StartDate, 'yyyy年MM月dd日'),
            FORMAT(@StartDate, 'yyyy年MM周'),
            FORMAT(@StartDate, 'yyyyMM'),
            DATENAME(WEEKDAY, @StartDate),
            DATEFROMPARTS(YEAR(@StartDate), 1, 1),
            DATEFROMPARTS(YEAR(@StartDate), 12, 31),
            DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3 - 2, 1),
            DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, DATEPART(QUARTER, @StartDate) * 3, @StartDate)))),
            DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1),
            EOMONTH(@StartDate)
        );

        SET @StartDate = DATEADD(DAY, 1, @StartDate);
    END
END

EXEC GenerateFutureDates;

请将上述代码中的YourDateDimensionTable替换为你的日期维度表的实际表名。然后，执行此存储过程来生成2024年、2025年和2026年的日期数据