获取每周的start_date和end_date

Question

这是我的代码

declare @startdate date='2021-01-01'
declare @enddate date='2021-12-31'
declare @week_end_date date
while (@startdate <= @enddate)
begin
if (datepart(week, @startdate) = 1)
select '01-01-2021'
else
set @startdate = DATEADD(day,1-datepart(dw,@startdate),@startdate)
select @startdate week_start_date
if (datepart(week, @startdate) = 53)
select '2021-12-31' 
else
set @week_end_date = dateadd(day, 7 - datepart(dw, @startdate), @startdate)
select @week_end_date week_end_date
set @startdate = dateadd(week, 1, @startdate)   
end

我想达到以下结果

week_start date             week_end_date
-----------------------------------------
2021-01-01                   2021-01-02
2021-01-03                   2021-01-09
2021-01-10                   2021-01-16
.
.
.
.
.
.
2021-12-26                  2021-12-31
.
.

我试图获得每周的start_date和end_date，但我希望第一周的开始日期为2020-01-01和上周的结束日期2020-12-31

在这两种情况下，else都在运行。

Answer 1

我猜你真的只需要覆盖整个日历年。前两个cte只是创建一个"数字表"。如果需要的话，你可以插入一个新的。我还假设datefirst设置为7。

with d as (
select n from (values (0), (1), (2), (3), (4), (5), (6), (7)) t(n)
), w as (
select d0.n + d1.n * 8 as offset from d as d0 cross join d as d1
), weeks as (
select
offset + 1 as weeknum,
dateadd(week, offset, basedate) as weekstart,
dateadd(day, 6, dateadd(week, offset, basedate)) as weekend
from w cross apply (
select dateadd(day, 1 - datepart(weekday, @startdate), @startdate)
) as v(basedate)
)
select
weeknum,
case when weekstart < @startdate then @startdate else weekstart end,
case when weekend   > @enddate   then @enddate   else weekend end
from weeks
where weekstart <= @enddate;

https://dbfiddle.uk/?rdbms=sqlserver_2014&小提琴= 5 f900ecd26b92e4a112f191730958cae

Answer 2

我猜您想要这样的东西，它将使所有日期保持在指定的范围内。硬编码到一年，快结束了，不是一个好的长期计划。

在Azure SQL中，你可以这样做

declare @startdate date='2021-01-01'
declare @enddate date='2021-12-31'
declare @curdate date=@startdate
declare @week_end_date date
while(@curdate<=@enddate)
begin
set @curdate= DATEADD(day,1-datepart(dw,@curdate),@curdate)
select GREATEST(@startdate,@curdate) week_start_date
set @week_end_date=dateadd(day,7-datepart(dw,@curdate),@curdate)
select LEAST(@enddate,@week_end_date) week_end_date
set @curdate= dateadd(week,1,@curdate)    
end

对于SQL Server 2019, GREATEST/LEAST尚未可用。

declare @startdate date='2021-01-01'
declare @enddate date='2021-12-31'
declare @curdate date=@startdate
declare @week_end_date date

while(@curdate<=@enddate)
begin
set @curdate= DATEADD(day,1-datepart(dw,@curdate),@curdate)
select IIF(@startdate>@curdate,@startdate,@curdate) week_start_date

set @week_end_date=dateadd(day,7-datepart(dw,@curdate),@curdate)
select IIF(@week_end_date > @enddate,@enddate, @week_end_date) week_end_date

set @curdate= dateadd(week,1,@curdate)    
end