数据仓库中的日期维度数据如何生成？

DECLARE @BegDate DATE;
SET @BegDate = '2005-1-1';
While @BegDate<='2012-12-31'
begin
insert into DimDate
SELECT CONVERT(VARCHAR(10), @BegDate, 112) AS DateKey,
CONVERT(VARCHAR(10), @BegDate, 120) AS Date,
Year(@BegDate) AS Year,
Case
When MONTH(@BegDate) between 1 and 6 then 'H1'
else 'H2'
end as SemiYear,
Month(@BegDate) AS MonthNumberOfYear,
CASE
WHEN Month(@BegDate) = 1 THEN 'Jan'
WHEN Month(@BegDate) = 2 THEN 'Feb'
WHEN Month(@BegDate) = 3 THEN 'Mar'
WHEN Month(@BegDate) = 4 THEN 'Apr'
WHEN Month(@BegDate) = 5 THEN 'May'
WHEN Month(@BegDate) = 6 THEN 'Jun'
WHEN Month(@BegDate) = 7 THEN 'Jul'
WHEN Month(@BegDate) = 8 THEN 'Aut'
WHEN Month(@BegDate) = 9 THEN 'Sep'
WHEN Month(@BegDate) = 10 THEN 'Oct'
WHEN Month(@BegDate) = 11 THEN 'Nov'
ELSE 'Dec'
END AS EnglishMonthName,
CASE
WHEN Month(@BegDate) = 1 THEN '一月'
WHEN Month(@BegDate) = 2 THEN '二月'
WHEN Month(@BegDate) = 3 THEN '三月'
WHEN Month(@BegDate) = 4 THEN '四月'
WHEN Month(@BegDate) = 5 THEN '五月'
WHEN Month(@BegDate) = 6 THEN '六月'
WHEN Month(@BegDate) = 7 THEN '七月'
WHEN Month(@BegDate) = 8 THEN '八月'
WHEN Month(@BegDate) = 9 THEN '九月'
WHEN Month(@BegDate) = 10 THEN '十月'
WHEN Month(@BegDate) = 11 THEN '十一月'
ELSE '十二月'
END AS ChinsesMonthName,
Day(@BegDate) AS DayNumberOfMonth,
Datepart(QQ, @BegDate) AS QuarterNumberOfYear,
Case
When MONTH(@BegDate) between 1 and 3 then 'Q1'
When MONTH(@BegDate) between 4 and 6 then 'Q2'
When MONTH(@BegDate) between 7 and 9 then 'Q3'
else 'Q4'
end as EnglishQuarterName,
Case
When MONTH(@BegDate) between 1 and 3 then '1季度'
When MONTH(@BegDate) between 4 and 6 then '2季度'
When MONTH(@BegDate) between 7 and 9 then '3季度'
else '4季度'
end as ChineseQuarterName,


Datepart(DW, @BegDate) AS DayNumberOfWeek,

Case
When Datepart(DW, @BegDate)=1 then 'Monday'
When Datepart(DW, @BegDate)=2 then 'Tuesday'
When Datepart(DW, @BegDate)=3 then 'Wednesday'
When Datepart(DW, @BegDate)=4 then 'Thurday'
When Datepart(DW, @BegDate)=5 then 'Friday'
When Datepart(DW, @BegDate)=6 then 'Saturday'
else 'Sunday'
end AS EnglishWeekDayName,

Case
When Datepart(DW, @BegDate)=1 then '星期一'
When Datepart(DW, @BegDate)=2 then '星期二'
When Datepart(DW, @BegDate)=3 then '星期三'
When Datepart(DW, @BegDate)=4 then '星期四'
When Datepart(DW, @BegDate)=5 then '星期五'
When Datepart(DW, @BegDate)=6 then '星期六'
else '星期日'
end AS ChineseWeekDayName,


Datepart(WK, @BegDate) AS WeekNumberOfYear,
CASE
WHEN DAY(@BegDate) <= 10 THEN 1
WHEN DAY(@BegDate) > 20 THEN 3
ELSE 2
END AS NumberOfTenDays,
CASE
WHEN DAY(@BegDate) <= 10 THEN '上旬'
WHEN DAY(@BegDate) > 20 THEN '下旬'
ELSE '中旬'
END AS TenDaysName,
DATENAME(DayOfYear,@BegDate) as DayOfYear
set @BegDate=dateadd(dd,1,@BegDate)
End