FirstDayofWeek and LastDayOfWeek on SQL Server

Question

我得到了一个奇怪的结果从我的一周的第一天和一周的最后一天在我的脚本，所以我有一个FirstDayOfWeek(开始周一)和LastDayOfWeek(结束周日)，但它有点奇怪，当日期开始在周日它指的是下一个星期。

例如:

• date = '2021-09-05' (Sunday)
• FirstDayOfWeek = '2021-09-06'(星期一)
• LastDayOfWeek = '2021-09-12' (Sunday)

我所期望的:

• date = '2021-09-05' (Sunday)
• FirstDayOfWeek = '2021-08-30'(星期一)
• LastDayOfWeek = '2021-09-05' (Sunday)<与日期相同>

这是我的查询:

declare @date_start datetime
set @date_start = '2021-09-05'
SELECT CAST(DATEADD(dd, -(DATEPART(dw, @date_start))+2, @date_start) AS DATE) as FirstDayOfWeek, 
CAST(DATEADD(dd, 8-(DATEPART(dw, @date_start)), @date_start) AS DATE) as LastDayOfWeek

Answer 1

试试这段代码!当然，您可以设置不同的日期，结果将如预期。

declare @date_start datetime
set @date_start = '2021-09-05'
SELECT DATEADD(week, DATEDIFF(week, 0, @date_start - 1), 0) AS FirstDayOfWeek,
DATEADD(day, 6, DATEADD(week, DATEDIFF(week, 0, @date_start - 1), 0)) AS LastDayOfWeek;

---

谢谢,