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.