T-SQL Code to Loop One Day at a Time
Jamey Johnston (@STATCowboy)
Hidden in my SQL Server 2016 Security Demo blog post is a neat T-SQL trick to loop through a date range day by day (check out the “2 – Oil&Gas RLS Demo – LoadTables.sql” script when you download the code)! But to give you a simpler example the gist of the code in the script is this:
DECLARE @StartDate DATE = ‘2016-10-01’;
DECLARE @EndDate DATE = ‘2016-10-31’;
WHILE (@StartDate <= @EndDate)
BEGIN
print @StartDate;
— Do Something like call a proc with the variable @StartDate
set @StartDate = DATEADD(day, 1, @StartDate);
END;
As you can see this is simple code and works like a charm! Hope you enjoy and hit me up on Twitter (@STATCowboy) if you have questions or improvements to the T-SQL Code!
Thanks it’s working great
sweet and short .easy to understandable.
good one.
What about if the dates are not evenly spaced? For example yesterday, one month ago, 3 months ago, 6 months ago, 1 year ago, 5 years ago…
That set of dates seems harder to loop through.
Andrew,
You could use a series of if statements or case statements to check where your @StartDate is before incrementing it at the bottom with a new value.
Example
— this is the initial set value for @StartDate at the top. Set to 30 days ago.
SET @StartDate = dateadd(day,datediff(DAY,30,GETDATE()),0);
While…
Begin..
Print something…
IF @StartDate = dateadd(day,datediff(DAY,30,GETDATE()),0)
BEGIN
SET @StartDate = dateadd(day,datediff(DAY,90,GETDATE()),0)
Select @StartDate
END
— sets it to 90 days ago
ELSE IF @StartDate = dateadd(day,datediff(DAY,90,GETDATE()),0)
BEGIN
SET @StartDate = dateadd(day,datediff(DAY,180,GETDATE()),0)
select @StartDate
END
— sets it to 180 days ago and so on.
It is definitely missing a few things but that is the basic concept on how you could do it.
This works great! Thank you so much!