DECLARE @Work_LegalHoliday TABLE
(
ID VARCHAR(36) ,
DateTimeName DATETIME ,
WeekName VARCHAR(50) ,
IsLegalHoliday INT ,
Remark NVARCHAR(MAX)
)
DECLARE @Year INT
SET @Year = 2021
--
;
WITH tbSource
AS ( SELECT CAST(RTRIM(@Year) + '-01-01' AS DATETIME) DateTimeName
UNION ALL
SELECT DATEADD(dd, 1, DateTimeName)
FROM tbSource
WHERE DATEPART(yy, DATEADD(dd, 1, DateTimeName)) = @Year
OR DATEPART(yy, DATEADD(dd, 1, DateTimeName)) = ( @Year +1 )
)
INSERT @Work_LegalHoliday
SELECT NEWID() ,
CONVERT(VARCHAR(10), DateTimeName, 121) ,
DATENAME(WEEKDAY, DateTimeName) ,
0 ,--2周休息1天标记
'' AS Remark
FROM tbSource a
WHERE DATEPART(WEEKDAY, a.DateTimeName) = 1
AND DATEPART(wk, a.DateTimeName) % 2 = 0
EXCEPT
SELECT NEWID() ,
CONVERT(VARCHAR(10), DateTimeName, 121) ,
DATENAME(WEEKDAY, DateTimeName) ,
0 ,
'' AS Remark
FROM @Work_LegalHoliday
OPTION ( MAXRECURSION 0 )
SELECT ID,DateTimeName,WeekName,IsLegalHoliday,Remark
FROM @Work_LegalHoliday
ORDER BY DateTimeName
