在创建时间维度的代码中添加 SET DATEFIRST 1，表示每周以周一开始
USE db_name
GO
SET NOCOUNT ON
-- 设置每周的起始天为周一
SET DATEFIRST 1
IF OBJECT_ID('table_name','U') IS NOT NULL
DROP TABLE table_name
GO
CREATE TABLE table_name
(
DateKey INT PRIMARY KEY,
FullDate DATE NOT NULL,
[DateName] NVARCHAR(20),
DayNumberOfWeek TINYINT NOT NULL,
DayNameOfWeek NVARCHAR(10) NOT NULL,
DayNumberOfMonth TINYINT NOT NULL,
DayNumberOfYear SMALLINT NOT NULL,
WeekNumberOfYear TINYINT NOT NULL,
EnglishMonthName NVARCHAR(10) NOT NULL,
MonthNumberOfYear TINYINT NOT NULL,
CalendarQuarter TINYINT NOT NULL,
CalendarSemester TINYINT NOT NULL,
CalendarYear SMALLINT NOT NULL
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '2021-01-01',
@EndDate = '2022-12-31'
WHILE (@StartDate <= @EndDate)
BEGIN
INSERT INTO table_name
(
DateKey,
FullDate,
[DateName],
DayNumberOfWeek,
DayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarSemester,
CalendarYear
)
SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,
--参数为112时间格式为YYYYMMDD
CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,
--参数为20时间格式为MM/DD/YY
CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],
--参数为106时间格式为MM DD YY
DATEPART(DW,@StartDate) AS DayNumberOfWeek,
DATENAME(DW,@StartDate) AS DayNameOfWeek,
DATENAME(DD,@StartDate) AS [DayOfMonth],
DATENAME(DY,@StartDate) AS [DayOfYear],
DATEPART(WW,@StartDate) AS WeekNumberOfYear,
DATENAME(MM,@StartDate) AS EnglishMonthName,
DATEPART(MM,@StartDate) AS MonthNumberOfYear,
DATEPART(QQ,@StartDate) AS CalendarQuarter,
CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
THEN 1
ELSE 2
END AS CalendarSemester,
DATEPART(YY,@StartDate) AS CalendarYear
SET @StartDate = @StartDate + 1
END
GO