Thursday, 10 January 2019

TSQL : Loop a date range

Useful for Incremental processing...
DECLARE @minDate DATE 
SET @minDate = '1997-02-13'
DECLARE @maxDate DATE 
SET @maxDate = GETDATE()

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @counterDate DATE
DECLARE @counterDateText VARCHAR(30)
DECLARE @MinCount INT
DECLARE @Stepsize INT

SELECT @Counter = DATEDIFF(dd,@minDate,@maxDate)
SELECT @MinCount = 0
SELECT @Stepsize = 1

WHILE @Counter >= @MinCount 

BEGIN 
SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
--RAISERROR (@CounterText, 10, 1) WITH NOWAIT  

SET @counterDate = DATEADD(dd,-@counter,@maxDate)
SET @counterDateText = CONVERT(VARCHAR(30),@counterDate,120)
RAISERROR (@counterDateText, 10, 1) WITH NOWAIT  

-- do useful stuff here

SELECT @Counter = @Counter - @Stepsize 
END