SQL创建日历 包含年月日/周/星期几

CREATE TABLE date
(
id INT IDENTITY (1,1),
date NVARCHAR(50),
year INT,
month INT,
day INT,
week INT ,
weekday NVARCHAR(10)

)

DECLARE @NowDATE DATETIME=‘2022.01.01 00:00:00’

DECLARE @i INT = 1
WHILE @i<=3650
BEGIN

INSERT INTO dbo.date
(
date,
year,
month,
day,
week,
weekday

)
VALUES
( CONVERT(varchar(12) ,@NowDATE, 102 ), – date - nvarchar(50)
DATEPART( YEAR, @NowDATE), – year - int
DATEPART( MONTH, @NowDATE), – month - int
DATEPART( DAY, @NowDATE), – day - int
DATEPART( WEEK, @NowDATE), – day - int
DATENAME(weekday,@NowDATE)
)
SELECT @NowDATE=DATEADD(DAY,1,@NowDATE)
SET @i =@i+1
PRINT CAST( @i AS NVARCHAR(5))
END

–按照 习惯，周日当做上周最后一天
UPDATE dbo.date SET week =week -1 WHERE weekday =‘星期日’
SELECT * FROM date