--创建Dim_DateCREATE TABLE [dbo].[Dim_Date](    [Datekey] [varchar](10) NOT NULL,    [Year] [int] NULL,    [Quarter] [int] NULL,    [QuarterCN] [varchar](8) NULL,    [Mnoth] [int] NULL,    [MonthCN] [varchar](6) NULL,    [Ten] [int] NULL,    [TenCN] [varchar](4) NULL,    [Week] [int] NULL,    [WeekDay] [nvarchar](30) NULL,    [Day] [int] NULL,    [Date] [varchar](10) NULL,PRIMARY KEY CLUSTERED (    [Datekey] 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

--清空Dim_DateTRUNCATE TABLE dbo.Dim_Date;--循环插入数据DECLARE @BeginDate DATE;SET @BeginDate=‘2009-1-1‘;WHILE @BeginDate<GETDATE()BEGININSERT INTO Dim_Date    SELECT CONVERT(VARCHAR(10),@BeginDate,112) AS Datekey, --主键    YEAR(@BeginDate) AS Year,--年    DATEPART(Quarter ,@BeginDate) AS Quarter,    CASE         WHEN DATEPART(Quarter ,@BeginDate)=1 THEN ‘第一季度‘         WHEN DATEPART(Quarter,@BeginDate)=2 THEN  ‘第二季度‘        WHEN DATEPART(Quarter ,@BeginDate)=3 THEN ‘第三季度‘         WHEN DATEPART(Quarter,@BeginDate)=4 THEN  ‘第四季度‘        END AS  QuarterCN,        MONTH(@BeginDate) AS Mnoth,    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