关于时间维表数据生成

有一个日期表的生产，通过函数写的，可以参考下

TRUNCATE TABLE dbo.Dim_Date;

DECLARE @BeginDate DATE;

SET @BeginDate = '2009-1-1';

WHILE @BeginDate<='2019-12-31'

BEGIN

INSERT INTO dbo.Dim_Date

SELECT CONVERT(VARCHAR(10), @BeginDate, 112) AS DateKey,

       Year(@BeginDate)                      AS Year,

       Datepart(QUARTER, @BeginDate)         AS Qu,

       CASE

         WHEN Datepart(QUARTER, @BeginDate) = 1 THEN '第一季度'

         WHEN Datepart(QUARTER, @BeginDate) = 2 THEN '第二季度'

         WHEN Datepart(QUARTER, @BeginDate) = 3 THEN '第三季度'

         ELSE '第四季度'

       END                                   AS QuCN,

       Month(@BeginDate)                     AS Month,

       CASE

         WHEN Month(@BeginDate) = 1 THEN '一月'

         WHEN Month(@BeginDate) = 2 THEN '二月'

         WHEN Month(@BeginDate) = 3 THEN '三月'

         WHEN Month(@BeginDate) = 4 THEN '四月'

         WHEN Month(@BeginDate) = 5 THEN '五月'

         WHEN Month(@BeginDate) = 6 THEN '六月'

         WHEN Month(@BeginDate) = 7 THEN '七月'

         WHEN Month(@BeginDate) = 8 THEN '八月'

         WHEN Month(@BeginDate) = 9 THEN '九月'

         WHEN Month(@BeginDate) = 10 THEN '十月'

         WHEN Month(@BeginDate) = 11 THEN '十一月'

         ELSE '十二月'

       END                                   AS MonthCN ,

       

 CASE

         WHEN DAY(@BeginDate) <=10 THEN 1

         WHEN DAY(@BeginDate) >20 THEN 3

         

         ELSE 2

       END                                   AS Ten,

        CASE

         WHEN DAY(@BeginDate) <=10 THEN '上旬'

         WHEN DAY(@BeginDate) >20 THEN '下旬'

         

         ELSE '中旬'

       END                                   AS TenCN,

       DATEPART(WEEK,@BeginDate) AS Week,

       DATEName(WEEKDAY,@BeginDate) AS WeekDay,

       DAY(@BeginDate) AS Day,

       CONVERT(VARCHAR(10), @BeginDate, 120) AS Date

       SET @BeginDate =DATEADD(D,1,@BeginDate)

       END;

自动的不知道，目前都是用sql函数，循环生成的

时分秒 和 年月日 拆开来
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimTime](
[DimTimeKey] [int] NOT NULL,
[Time] varchar NOT NULL,
[Time24] varchar NOT NULL,
[Hour] [tinyint] NOT NULL,
[HourName] varchar NOT NULL,
[Minute] [tinyint] NOT NULL,
[MinuteKey] [int] NOT NULL,
[MinuteName] varchar NOT NULL,
[Second] [tinyint] NOT NULL,
[Hour24] [tinyint] NOT NULL,
[AM] char NOT NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[DimTimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

---------------------------------------------------

declare @DimTimeKey int, @Date datetime, @Am char(2),
@hour24 tinyint, @hour tinyint, @minute tinyint, @second int
set @DimTimeKey = 0

while @DimTimeKey < (606024)
begin
set @DimTimeKey = @DimTimeKey + 1
set @Date = DATEADD(second,@DimTimeKey,convert(datetime, '1/1/2007'))
set @Am = right(convert(varchar,@Date,109),2)
set @hour24 = DATEPART(hour, @Date)
set @hour = case when @Am = 'PM' then @hour24 - 12 else @hour24 end
set @minute = DATEPART(minute, @Date)
set @second = DATEPART(second, @Date)

insert into dbo.DimTime(DimTimeKey,Time,Time24,HourName,MinuteName,MinuteKey,Hour,Hour24,Minute,Second,AM)
select @DimTimeKey,
Time = right('0' + convert(varchar,@hour),2)
+ ':' + right('0' + convert(varchar,@minute),2)
+ ':' + right('0' + convert(varchar,@second),2) + ' ' + @Am,
Time24 = convert(varchar,@Date,108),
HourName = right('0' + convert(varchar,@hour),2) + ' ' + @Am,
MinuteName = right('0' + convert(varchar,@hour),2)
+ ':' + right('0' + convert(varchar,@minute),2)+ ' ' + @Am,
MinuteKey = (@hour24*60) + @minute,
@hour, @hour24, @minute, @second, @Am
end

select *
from DimTime
where AM = 'AM'