建立时间维度表

when (DATEPART(month, @b1))='4' then N'四月'
when (DATEPART(month, @b1))='5' then N'五月'
when (DATEPART(month, @b1))='6' then N'六月'
when (DATEPART(month, @b1))='7' then N'七月'
DECLARE @b1 DATETIME
set @b1='2001-01-01' --设置起始日期
WHILE @b1<'2021-01-01' --设置截止日期
BEGIN
INSERT INTO dbo.[Time] (
[DateKey],
[Date],
[Year],
when (DATEPART(month, @b1))='10' then 'October'
when (DATEPART(month, @b1))='11' then 'November'
else 'December'
END,
CASE --Month Short En 6
when (DATEPART(month, @b1))='8' then N'八月'
when (DATEPART(month, @b1))='9' then N'九月'
when (DATEPART(month, @b1))='10' then N&PART(month, @b1))='11' then N'十一月'
when (DATEPART(month, @b1))='9' then 'Sep'
when (DATEPART(month, @b1))='10' then 'Oct'
when (DATEPART(month, @b1))='11' then 'Nov'
else 'Dec'
[Month],
[Month EN],
[Month Short EN],
[Month CN],
[Day],
[Quarter],
[Quarter EN],
[Quarter CN],
[Weekday],
[Weekday EN],
[Weekday Short EN],
[Work Week] [float] NULL,
[Work Week Code] [nvarchar](50) NULL,
[Weekend] [nvarchar](5) NULL
) ON [PRIMARY]
GO
SET DATEFIRST 1 --设周一为每周的第一天
GO
--向时间表插入数据
[Period of Ten Days] [nvarchar](10) NULL,
[Work Year] [float] NULL,
[Work Month] [float] NULL,
[Work Month EN] [nvarchar](50) NULL,
[Work Month Short EN] [nvarchar](50) NULL,
[Work Week],
[Work Week Code],
[Weekend]
)
VALUES(
CONVERT(NVARCHAR(10),@b1,112), --DateKey 1
@b1, --Date 2
DATEPART(year, @b1), --Year 3
when DATEName (qq, @b1)='2' then 'Q2'
when DATEName (qq, @b1)='3' then 'Q3'
else 'Q4'
END,
CASE --quarter cn 11
when DATEName (qq, @b1)='1' then N'一季度'
when DATEPART(dw, @b1)=4 then N'星期四'
when DATEPART(dw, @b1)=5 then N'星期五'
when DATEPART(dw, @b1)=6 then N'星期六'
else N'星期天'
END,
END,
CASE --Period of Ten Days 19
when DATEName (dd, @b1)<=10 then N'上旬'
when DATEName (dd, @b1)>20 then N'下旬'
else N'中旬'
END,
when (DATEPART(month, @b1))='1' then 'Jan'
when (DATEPART(month, @b1))='2' then 'Feb'
when (DATEPART(month, @b1))='3' then 'Mar'
when (DATEPART(month, @b1))='4' then 'Apr'
when DATEName (qq, @b1)='2' then N'二季度'
when DATEName (qq, @b1)='3' then N'三季度'
else N'四季度'
END,
DATEPART(dw, @b1),--Weekday 12
DATENAME (dw, @b1),--Weekday EN 13
[Year] [float] NULL,
[Month] [float] NULL,
[Month EN] [nvarchar](50) NULL,
[Month Short EN] [nvarchar](50) NULL,
[Month CN] [nvarchar](50) NULL,
CASE --Weekday Short EN 14 --注意,周日是第一天.
when DATEPART(dw, @b1)='1' then 'Mon'
when DATEPART(dw, @b1)='2' then 'Tue'
when DATEPART(dw, @b1)='3' then 'Wed'
[Weekday CN],
[Week of Year],
[Day of Year],
[SemiYearly],
[Period of Ten Days],
[Work Year],
[Work Month],
[Work Month EN],
[Work Month Short EN],
when (DATEPART(month, @b1))='3' then 'March'
when (DATEPART(month, @b1))='4' then 'April'
when (DATEPART(month, @b1))='5' then 'May'
when (DATEPART(month, @b1))='6' then 'June'
END,
CASE --Month CN 7
when (DATEPART(month, @b1))='1' then N'一月'
when (DATEPART(month, @b1))='2' then N'二月'
when (DATEPART(month, @b1))='3' then N'三月'
--创建时间表
IF OBJECT_ID('dbo.Time') IS NOT NULL
DROP TABLE dbo.[Time]
GO
CREATE TABLE [dbo].[Time](
[DateKey] [nvarchar](10) NULL,
[Date] [datetime] NULL,
when DATEPART(dw, @b1)='4' then 'Thu'
when DATEPART(dw, @b1)='5' then 'Fri'
when DATEPART(dw, @b1)='6' then 'Sat'
else 'Sun'
END,
DATEName (wk, @b1),--week of year 16
DATEName (dy, @b1),--day of year 17
CASE --SemiYearly 18
when DATEPART(month, @b1)<=6 then N'上半年'
else N'下半年'
[Day] [float] NULL,
[Quarter] [float] NULL,
[Quarter EN] [nvarchar](50) NULL,
[Quarter CN] [nvarchar](50) NULL,
[Weekday] [float] NULL,
[Weekday EN] [nvarchar](50) NULL,
DATEPART(year, @b1),--Work Year 20
DATEName (dd, @b1),--day of month 21
CASE --work month en 21
when (DATEPART(month, @b1))='1' then 'January'
CASE --Weekday CN 15
when DATEPART(dw, @b1)=1 then N'星期一'
when DATEPART(dw, @b1)=2 then N'星期二'
when DATEPART(dw, @b1)=3 then N'星期三'
when (DATEPART(month, @b1))='5' then 'May'
when (DATEPART(month, @b1))='6' then 'Jun'
when (DATEPART(month, @b1))='7' then 'Jul'
when (DATEPART(month, @b1))='8' then 'Aug'
when (DATEPART(month, @b1))='7' then 'July'
when (DATEPART(month, @b1))='8' then 'August'
when (DATEPART(month, @b1))='9' then 'September'
DATEPART(month, @b1), --Month 4
CASE --Month EN 5
when (DATEPART(month, @b1))='1' then 'January'
when (DATEPART(month, @b1))='2' then 'February'
[Weekday Short EN] [nvarchar](50) NULL,
[Weekday CN] [nvarchar](50) NULL,
[Week of Year] [float] NULL,
[Day of Year] [float] NULL,
[SemiYearly] [nvarchar](50) NULL,
else N'十二月'
END,
DATEPART(day, @b1),--day 8
DATEName (qq, @b1),--quarter 9
CASE --quarter en 10
when DATEName (qq, @b1)='1' then 'Q1'