Loop Through Dates in T-SQL … One Day at a Time!

 

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;

Download code!

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!

4 Replies to “Loop Through Dates in T-SQL … One Day at a Time!”

  1. 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.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.