SQL生成日期维度(到小时)

#建表语句：

CREATE TABLE [dbo].[Dim_日期3](
    [日期3ID] [varchar](10) NOT NULL,
    [年] [int] NULL,
    [半年] [varchar](6) NULL,
    [季] [varchar](2) NULL,
    [月] [varchar](4) NULL,
    [周] [varchar](6) NULL,
    [星期] [varchar](6) NULL,
    [是否周末] [varchar](4) NULL,
    [日] [int] NULL,
    [时] [int] NULL,
 CONSTRAINT [PK_Dim_日期3] PRIMARY KEY CLUSTERED 
(
    [日期3ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

#生成数据：

DECLARE @BeginDate DATE;

SELECT @BeginDate = '2013-1-1';

WHILE @BeginDate <= '2014-12-31'
  BEGIN
      DECLARE @hour INT
      DECLARE @hourChar VARCHAR(2)

      SET @hour=0
      SET @BeginDate = Dateadd(DAY, 1, @BeginDate);

      WHILE @hour < 24
        BEGIN
            SET @hourChar=RIGHT(CONVERT(VARCHAR(3), 100+@hour), 2)

            INSERT INTO [Dim_日期3]
            SELECT CONVERT(VARCHAR(8), @BeginDate, 112) + @hourChar AS [日期ID],
                   Year(@BeginDate)                                 AS [年],
                   CASE
                     WHEN Datepart(month, @BeginDate) <= 6 THEN '上半年'
                     ELSE '下半年'
                   END                                              AS [半年],
                   CASE
                     WHEN Datename (qq, @BeginDate) = '1' THEN 'Q1'
                     WHEN Datename (qq, @BeginDate) = '2' THEN 'Q2'
                     WHEN Datename (qq, @BeginDate) = '3' THEN 'Q3'
                     ELSE 'Q4'
                   END                                              AS [季],
                   CASE
                     WHEN Month(@BeginDate) = 1 THEN '01月'
                     WHEN Month(@BeginDate) = 2 THEN '02月'
                     WHEN Month(@BeginDate) = 3 THEN '03月'
                     WHEN Month(@BeginDate) = 4 THEN '04月'
                     WHEN Month(@BeginDate) = 5 THEN '05月'
                     WHEN Month(@BeginDate) = 6 THEN '06月'
                     WHEN Month(@BeginDate) = 7 THEN '07月'
                     WHEN Month(@BeginDate) = 8 THEN '08月'
                     WHEN Month(@BeginDate) = 9 THEN '09月'
                     WHEN Month(@BeginDate) = 10 THEN '10月'
                     WHEN Month(@BeginDate) = 11 THEN '11月'
                     ELSE '12月'
                   END                                              AS [月],
                   CASE
                     WHEN Datepart(WEEK, @BeginDate) < 10 THEN '第0' + CONVERT(VARCHAR(2), Datepart(WEEK, @BeginDate)) + '周'
                     ELSE '第' + CONVERT(VARCHAR(2), Datepart(WEEK, @BeginDate)) + '周'
                   END                                              AS [周],
                   Datename(WeekDay, @BeginDate)                    AS [星期],
                   CASE
                     WHEN Datepart(dw, @BeginDate) = '6' THEN '周末'
                     WHEN Datepart(dw, @BeginDate) = '7' THEN '周末'
                     ELSE '平时'
                   END                                              AS '是否周末',
                   Day(@BeginDate)                                  AS [日],
                   @hour                                            AS [时]

            SET @hour=@hour + 1
        END
  END;