Create dynamic date resultset for joining

/* ------------------------------------------------
Function: dbo.GetDateRange
Author: Paul Chapman (chappi (dot) uk (at) virgin (dot) net)
Date: 05/03/2004
Purpose: Dynamically generate a resultset of dates for a given range/increment
------------------------------------------------ */
CREATE FUNCTION dbo.GetDateRange(
@startdate datetime,
@enddate datetime,
@increment int = 1)
RETURNS @DateRange TABLE (datevalue datetime)
AS
BEGIN
DECLARE @loopdate datetime
SELECT @loopdate = @startdate
WHILE (@loopdate <= @enddate)
BEGIN
INSERT INTO @DateRange VALUES (@loopdate)
SELECT @loopdate = DATEADD(d, @increment, @loopdate)
END
RETURN
END

Example



DECLARE @startdate datetime, @enddate datetime
SELECT @startdate = GETDATE()
SELECT @enddate = DATEADD(yyyy, 1, @startdate)
SELECT dateValue, "Day_Delta"=DATEDIFF(dd, GETDATE(), dateValue) from dbo.GetDateRange(@startDate, @endDate, 7)

dateValue Day_Delta
------------------------------------------------------ -----------
2004-03-06 12:18:36.577 0
2004-03-13 12:18:36.577 7
2004-03-20 12:18:36.577 14
2004-03-27 12:18:36.577 21
2004-04-03 12:18:36.577 28
2004-04-10 12:18:36.577 35
2004-04-17 12:18:36.577 42
2004-04-24 12:18:36.577 49
2004-05-01 12:18:36.577 56
2004-05-08 12:18:36.577 63
2004-05-15 12:18:36.577 70
2004-05-22 12:18:36.577 77
2004-05-29 12:18:36.577 84
2004-06-05 12:18:36.577 91
2004-06-12 12:18:36.577 98
2004-06-19 12:18:36.577 105
2004-06-26 12:18:36.577 112
2004-07-03 12:18:36.577 119
2004-07-10 12:18:36.577 126
2004-07-17 12:18:36.577 133
2004-07-24 12:18:36.577 140
2004-07-31 12:18:36.577 147
2004-08-07 12:18:36.577 154
2004-08-14 12:18:36.577 161
2004-08-21 12:18:36.577 168
2004-08-28 12:18:36.577 175
2004-09-04 12:18:36.577 182
2004-09-11 12:18:36.577 189
2004-09-18 12:18:36.577 196
2004-09-25 12:18:36.577 203
2004-10-02 12:18:36.577 210
2004-10-09 12:18:36.577 217
2004-10-16 12:18:36.577 224
2004-10-23 12:18:36.577 231
2004-10-30 12:18:36.577 238
2004-11-06 12:18:36.577 245
2004-11-13 12:18:36.577 252
2004-11-20 12:18:36.577 259
2004-11-27 12:18:36.577 266
2004-12-04 12:18:36.577 273
2004-12-11 12:18:36.577 280
2004-12-18 12:18:36.577 287
2004-12-25 12:18:36.577 294
2005-01-01 12:18:36.577 301
2005-01-08 12:18:36.577 308
2005-01-15 12:18:36.577 315
2005-01-22 12:18:36.577 322
2005-01-29 12:18:36.577 329
2005-02-05 12:18:36.577 336
2005-02-12 12:18:36.577 343
2005-02-19 12:18:36.577 350
2005-02-26 12:18:36.577 357
2005-03-05 12:18:36.577 364

(53 row(s) affected)

thanks, that could be very handy.

Tom Pullen
DBA, Oxfam GB