T-SQL从dates表中选择过去n个月的周日到周六(相隔2周)的日期

DECLARE @n INT = 3; -- 假设选择过去3个月的日期
DECLARE @startDate DATE = DATEADD(MONTH, -@n, GETDATE()); -- 计算起始日期
DECLARE @endDate DATE = GETDATE(); -- 结束日期为当前日期

WITH cte AS (
  SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, date), date) AS startDate,
         DATEADD(DAY, 7 - DATEPART(WEEKDAY, date), date) AS endDate
  FROM dates
  WHERE date >= @startDate AND date <= @endDate
)
SELECT startDate, endDate
FROM cte
WHERE DATEDIFF(WEEK, startDate, endDate) = 2
  AND DATEPART(WEEKDAY, startDate) = 1 -- 周日
  AND DATEPART(WEEKDAY, endDate) = 6 -- 周六
ORDER BY startDate;