If your dates are no more than 2047 days apart:
declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)
select dateadd(day, number, @dt)
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @dt) < @dtEnd
I updated my answer after several requests to do so. Why?
The original answer contained the subquery
select distinct number from master.dbo.spt_values
where name is null
which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.
However, as I tried to analyze the code that MSSQL internally when querying from spt_values
, I found that the SELECT
statements always contain the clause WHERE [type]='[magic code]'
.
Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:
There may be a future version of SQL Server which defines a different [type]
value which also has NULL
as values for [name]
, outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…