Generate a resultset of incrementing dates in TSQL

Question

Consider the need to create a resultset of dates. We've got start and end dates, and we'd like to generate a list of dates in between.

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (@Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'

--need to fill @AllDates. Trying to avoid looping. 
-- Surely if a better solution exists.

Consider the current implementation with a WHILE loop:

DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
 INSERT INTO @AllDates VALUES (@dCounter)
 SELECT @dCounter=@dCounter+1 
END

Question: How would you create a set of dates that are within a user-defined range using T-SQL? Assume SQL 2005+. If your answer is using SQL 2008 features, please mark as such.

Answer 1

What I'd recommend: create an auxiliary table of numbers and use it to generate your list of dates. You can also use a recursive CTE, but that may not perform as well as joining to an auxiliary table of numbers. See SQL, Auxiliary table of numbers for info on both options.

Answer 2

Try this. No Looping, CTE limits, etc. and you could have just about any no. of records generated. Manage the cross-join and top depending upon what is required.

select top 100000 dateadd(d,incr,'2010-04-01') as dt from
(select  incr = row_number() over (order by object_id, column_id), * from
(
select a.object_id, a.column_id from  sys.all_columns a cross join sys.all_columns b
) as a
) as b

Please note the nesting is for easier control and conversion into views, etc.

Answer 3

This solution is based on marvelous answer of the same question for MySQL. It is also very performant on MSSQL. https://stackoverflow.com/a/2157776/466677

select DateGenerator.DateValue from (
  select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue
  from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a
  cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b
  cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c
  cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d
) DateGenerator
WHERE DateGenerator.DateValue BETWEEN 'Mar 1 2009' AND 'Aug 1 2009'
ORDER BY DateGenerator.DateValue ASC

works only for dates in the past, for dates in future change minus sign in DATEADD function. Query works only for SQL Server 2008+ but could be rewritten also for 2005 by replacing "select from values" construct with unions.

Answer 4

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.

Answer 5

While I really like KM's solution above (+1), I must question your "no loop" assumption - given the plausible date ranges that your app will work with, having a loop should not really be all that expensive. The main trick is to strore the results of the loop in staging/cache table, so that extremely large sets of queries do not slow down the system by re-calculating the same exact dates. E.g. each query only computes/caches the date ranges that are NOT already in cache and that it needs (and pre-populate the table with some realistic date range like ~2 years in advance, with range determined by your application business needs).

Answer 6

Really like Devio's solution as I needed exactly something like this that needs to run on SQL Server 2000 (so cannot use CTE) however, how could it be modified to ONLY generate dates that align with a given set of days of the week. For example, I only want the dates that fall in line with Monday, Wednesday and Friday or whatever particular sequence I choose based on the following number Scheme:

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

Example:

StartDate = '2015-04-22' EndDate = '2017-04-22' --2 years worth
Filter on: 2,4,6 --Monday, Wednesday, Friday dates only

What I'm trying to code is to add two additional fields: day,day_code Then filter the generated list with a condition...

I came up with the following:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 1095, @dt)

select dateadd(day, number, @dt) as Date, DATENAME(DW, dateadd(day, number, @dt)) as Day_Name into #generated_dates
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd 

select * from #generated_dates where Day_Name in ('Saturday', 'Friday')

drop table #generated_dates