将星期四和星期三作为SQL Server一周的开始日期和结束日期

Question

我在将一周的开始和结束日期转换为星期四和星期三到SQL Server中遇到了一点困难。

我已经想出了一个代码来提取本周的星期四和星期三

SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 3) ThursdayOfCurrentWeek

然而，问题是当前星期四的一周开始应该是它本身，但代码返回的是上周的星期四。

例如，最好是:

29/09/2021 (23/09/2021-29/09/2021);
30/09/2021 (30/09/2021-06/09/2021)

但是SQL会返回:

29/09/2021 (23/09/2021-29/09/2021);
30/09/2021 (23/09/2021-29/09/2021)

我能做到吗?

Answer 1

…

select 
_date, datename(weekday, _date) _dateweekday, 
prvthursday, datename(weekday, prvthursday),
cast(prvthursday as date) as week_start, cast(dateadd(day, 6, prvthursday) as date) as week_end
from
(
select *, dateadd(day, -(7+datepart(weekday, dateadd(day, @@datefirst, _date))-5/*<-- prev thursday:5, prev sat:7, prev monday:2 etc*/)%7, _date) as prvthursday
from
(
--dates
select dateadd(day, row_number() over(order by @@spid), '20210101') as _date
from sys.all_objects
) as d
) as src;