I’ve written the script below at least once a month, for either reports, or working with large data sets. Rather than reinventing the wheel, I’m posting my code here and to GitHub for everyone to see.
The script expects two date parameters, a start date and an overall end date. The script will then assign a third variable, end date, seven days after the start date. The script will execute your given code. The final step inside the loop increments the start date and end date by seven days. The script will loop until the start date is greater then the overall end date.
declare @StartDate date
declare @EndDate date
declare @OverallEndDate date
declare @PrintStart varchar(10)
declare @PrintEnd varchar(10)
-- Set the start date and overall end date
-- Set the end date to 7 days after the start date
set @OverallEndDate = '2018-12-31'
set @StartDate = '2018-01-01'
set @EndDate = dateadd( dd,7 ,@startDate)
-- Loop while the start date is less than the overall end date
while(@OverallEndDate > @StartDate)
begin
-- Print the working weeks
set @PrintStart = CONVERT( varchar(10),@StartDate, 120)
set @PrintEnd = CONVERT( varchar(10),@EndDate, 120)
raiserror('Working on %s to %s' ,10, 1,@PrintStart ,@PrintEnd)
-- Do something here
-- select [SalesOrderID]
-- from [Sales].[SalesOrderHeader]
-- where [OrderDate] >= @StartDate
-- and [OrderDate] < @EndDate
-- Increment the weeks
set @StartDate = @EndDate
set @EndDate = dateadd( dd,7 ,@startDate)
end
raiserror('Finished!' ,10, 1)
As you can see, the loop iterates over all dates, and completes successfully.
Follow this project on GitHub: 
