Generate Date Range

CREATE TABLE #NumRange(
n int
)

DECLARE @StartDatetime datetime
DECLARE @EndDatetime datetime
DECLARE @I int

SET NOCOUNT ON

SET @I = 0
WHILE @I <= 9 BEGIN
INSERT INTO #NumRange VALUES(@I)
SET @I = @I + 1
END


SET @StartDatetime = '05-01-1996'
SET @EndDatetime = '09-01-2002'

--SELECT DATEDIFF(dd, @StartDatetime, @EndDatetime)

SELECTMyDatetime = DATEADD(dd, t.num, @StartDatetime)
FROM(
SELECTnum = a.n +
(b.n * 10) +
(c.n * 100) +
(d.n * 1000) +
(e.n * 10000) +
(f.n * 100000)
FROM#NumRange a
CROSS JOIN #NumRange b
CROSS JOIN #NumRange c
CROSS JOIN #NumRange d
CROSS JOIN #NumRange e
CROSS JOIN #NumRange f
) t
WHEREDATEADD(dd, t.num, @StartDatetime) <= @EndDatetime
ORDER BY DATEADD(dd, t.num, @StartDatetime)

DROP TABLE #NumRange