SQLSERVER - 计算在某一段时间内周日的所有日期

DECLARE @Date datetime

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @WeekDay int

DECLARE @i int

CREATE TABLE #T ([Date] NVARCHAR(10))

SET DATEFIRST 7 --设置每周的第一天 

SET @StartDate='2000-01-01' --统计的开始日期

SET @EndDate='2020-12-31' --统计的结束日期

SET @WeekDay=1 --根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二

SET @i=DATEPART(weekday,@StartDate)

IF(@i<=@WeekDay AND @i<7)

SET @i=@WeekDay-@i 

ELSE IF(@i<=@WeekDay AND @i=7)

SET @i=@i-@WeekDay

ELSE

SET @i=@@DATEFIRST-@i+@WeekDay

SET @Date=DATEADD(day,@i,@StartDate)

WHILE @Date<=@EndDate 

BEGIN

IF(@StartDate<=@Date) INSERT INTO #T SELECT CONVERT(nvarchar(10),@Date,121) 

SET @Date=DATEADD(Week,1,@Date)

END

SELECT * FROM #T

DROP TABLE #T

GO