教你用SQL如何生成一张日期维度表

CREATE TABLE RQ_TABLE 
   ( 
       COUNTRY VARCHAR(5),--'国家' 
       City VARCHAR(5) ,--'地市' 
       DATE_NAME VARCHAR(20) ,--'日历日期' 
       YEAR CHAR(4),--'年' 
       MONTH CHAR(2),--'月' 
       DAY CHAR(2),--'日' 
       WEEK CHAR(6),--'星期' 
       QUARTER varchar(2) NULL,--季度
       FWEEK varchar(6) NULL,--一年中的第几周,国外周日是一周的第一天,周六是最后一天
       Weekend varchar(20) NULL,--是否为周末
       CREATOR VARCHAR(32),--'创建人' 
       CREATE_DATE DATETIME ,--'创建日期' 
       CREATE_INST VARCHAR(20),--'创建公众号' 
       DEL_FLAG CHAR(1) DEFAULT 0 ,--'删除标志' 0:不删除，1:已删除 
       TS DATETIME DEFAULT GETDATE()--'时间戳' 
   )

CREATE PROC PROC_RQ(@YEAR INT ) 
AS 
BEGIN 
DECLARE @i INT; 
DECLARE @START_DATE VARCHAR(20); 
DECLARE @END_DATE VARCHAR(20); 
DECLARE @DATE_COUNT INT; 

  SET @i=0;   
--定义一年的开始日期，用CONCAT函数将年份和月份日期拼接起来 
  SET @START_DATE= CONCAT(@YEAR, '-01-01'); 
--定义一年的结束日期 
  SET @END_DATE = CONCAT(@YEAR+1,'-01-01'); 
--如果表内已经存在要新建年份的日历，则存储过程要先删除 
  DELETE FROM RQ_TABLE WHERE YEAR = @YEAR; 
--计算开始到结束日期一共有多少天，DATEDIFF函数计算日期的间隔天数 
  SET @DATE_COUNT = DATEDIFF(DAY,@START_DATE,@END_DATE); 
 
--建立循环，条件是@i小于一年的天数   
  WHILE @i < @DATE_COUNT 
  BEGIN 
      INSERT INTO RQ_TABLE 
    (COUNTRY,City,DATE_NAME,YEAR,MONTH,WEEK,DAY,QUARTER,FWEEK,Weekend,
    CREATOR,CREATE_DATE,CREATE_INST)   
       SELECT 
       '中国', 
       '郑州', 
      --CONVERT函数将@START_DATE转换成指定的格式 
       CONVERT(CHAR(10),@START_DATE,111) DATE_NAME, 
      --DATENAME获取@START_DATE的年份 
       CONVERT(CHAR(4),DATENAME(YEAR, @START_DATE)) YEAR, 
      --DATENAME获取@START_DATE的月份 
       CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) MONTH, 
      --DATENAME获取@START_DATE的星期 
       CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) WEEK, 
      --DATENAME获取@START_DATE的日期 
       CONVERT(CHAR(2),DATENAME(DAY, @START_DATE)) DAY, 
     CASE
           WHEN CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) IN('01','02','03') THEN 'Q1'
           WHEN CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) IN('04','05','06') THEN 'Q2'
           WHEN CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) IN('07','08','09') THEN 'Q3'
           ELSE 'Q4'
                END                                              AS [季],
--国外周日为一周的第一天,周六为一周的最后一天
      '第' + CONVERT(VARCHAR(2), Datepart(WEEK, @START_DATE)) + '周'  AS [周],
--判断是否是周末
       CASE
         WHEN CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) = '星期六' THEN '周末'
           WHEN CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) = '星期日' THEN '周末'
           ELSE '平时'   
                END                                              AS '是否周末',
       '蓝涩街灯', 
--获取当前创建日期维度表时间
       GETDATE() CREATE_DATE, 
       'SQL数据库运维' 
      --插入完成后，对@i进行自加1处理 
      SET @i=@i+1;   
      --更新@START_DATE的值，在原来的基础上使用DATEADD函数增加一天 
      SET @START_DATE=CONVERT(CHAR(10),DATEADD(DAY, 1, @START_DATE),120); 
  END 
END 

--执行存储过程,根据输入的年份生成相对应年份的日历表
EXEC PROC_RQ 2021

--查询日历表信息
SELECT * FROM RQ_TABLE ORDER BY DATE_NAME ASC