DECLARE @table TABLE
(
id INT IDENTITY,
CreateDate DATE
);
INSERT INTO @table
(
CreateDate
)
SELECT ‘2020-06-07‘--上周日
UNION ALL
SELECT ‘2020-06-08‘--本周一
UNION ALL
SELECT ‘2020-06-09‘--本周二
UNION ALL
SELECT ‘2020-06-10‘--本周三
UNION ALL
SELECT ‘2020-06-11‘--本周四
UNION ALL
SELECT ‘2020-06-12‘--本周五
UNION ALL
SELECT ‘2020-06-13‘--本周六
UNION ALL
SELECT ‘2020-06-14‘--本周日
UNION ALL
SELECT ‘2020-06-15‘--下周一
DECLARE @date DATE = ‘2020-06-12‘;--本周五
DECLARE @wkStart DATE;
SET @wkStart = DATEADD(DAY, - (DATEPART(WEEKDAY, @date) - 2), @date);
IF (@wkStart > @date)
BEGIN
SET @wkStart = DATEADD(WEEK, -1, @wkStart);
END;
DECLARE @wkend DATE = DATEADD(WEEK, 1, @wkStart);
--截止到今天自然周数据
SELECT *,DATENAME(dw, CONVERT(DATETIME,CreateDate) + @@DateFirst) time
FROM @table
WHERE CreateDate >= @wkStart
AND CreateDate < @wkend
AND DATEDIFF(DAY, CreateDate, GETDATE()) >= 0;
--本周自然周数据
SELECT *,DATENAME(dw, CONVERT(DATETIME,CreateDate) + @@DateFirst ) time
FROM @table
WHERE CreateDate >= @wkStart
AND CreateDate < @wkend
原文：https://www.cnblogs.com/dengjb/p/13092499.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群