Generate a resultset of incrementing dates in TSQL

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.

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.

Tthe following uses a recursive CTE (SQL Server 2005+):

WITH dates AS (
     SELECT CAST('2009-01-01' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT ...
  FROM TABLE t
  JOIN dates d ON d.date = t.date --etc.

@KM's answer creates a numbers table first, and uses it to select a range of dates. To do the same without the temporary numbers table:

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

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

WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
     Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
     Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
     Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

	SELECT @Start+n-1 as Date
		FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
			FROM Nbrs ) D ( n )
	WHERE n <= DATEDIFF(day,@Start,@End)+1 ;

Test of course, if you are doing this often, a permanent table may well be more performant.

The query above is a modified version from this article, which discusses generating sequences and gives many possible methods. I liked this one as it does not create a temp table, and is not limited to the number of elements in the sys.objects table.

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.

For this method to work, you need to do this one time table setup:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, use this query:

SELECT
    @Start+Number-1
    FROM Numbers
    WHERE Number<=DATEDIFF(day,@Start,@End)+1

to capture them do:

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

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

INSERT INTO @AllDates
        (Date)
    SELECT
        @Start+Number-1
        FROM Numbers
        WHERE Number<=DATEDIFF(day,@Start,@End)+1

SELECT * FROM @AllDates

output:

Date
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
....
2009-07-25 00:00:00.000
2009-07-26 00:00:00.000
2009-07-27 00:00:00.000
2009-07-28 00:00:00.000
2009-07-29 00:00:00.000
2009-07-30 00:00:00.000
2009-07-31 00:00:00.000
2009-08-01 00:00:00.000

(154 row(s) affected)

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.

Another option is to create corresponding function in .NET. Here's how it looks like:

[Microsoft.SqlServer.Server.SqlFunction(
  DataAccess = DataAccessKind.None,
  FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow",
  IsDeterministic = true,
  IsPrecise = true,
  SystemDataAccess = SystemDataAccessKind.None,
  TableDefinition = "d datetime")]
public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate)
{
	// Check if arguments are valid

	int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366);
	List<DateTime> res = new List<DateTime>();
	for (int i = 0; i <= numdays; i++)
		res.Add(dtStart.Value.AddDays(i));

	return res;
}

public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d)
{
	d = (DateTime)row;
}

This is basically a prototype and it can be made a lot smarter, but illustrates the idea. From my experience, for a small to moderate time spans (like a couple of years) this function performs better than the one implemented in T-SQL. Another nice feature of CLR version is that it does not creates temporary table.

Overview

Here's my version (2005 compatible). The advantages of this approach are:

• you get a general purpose function which you can use for a number of similar scenarios; not restricted to just dates
• the range isn't limited by the contents of an existing table
• you can easily change the increment (e.g. get the date every 7 days instead of every day)
• you don't require access to other catalogs (i.e. master)
• the sql engine's able to do some optimisation of the TVF that it couldn't with a while statement
• generate_series is used in some other dbs, so this may help make your code instinctively familiar to a wider audience

SQL Fiddle: http://sqlfiddle.com/#!6/c3896/1

Code

A reusable function for generating a range of numbers based on given parameters:

create function dbo.generate_series
(
	  @start bigint
	, @stop bigint
	, @step bigint = 1
	, @maxResults bigint = 0 --0=unlimitted
)
returns @results table(n bigint)
as
begin

	--avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
	if @step = 0 return
	if @start > @stop and @step > 0 return
	if @start < @stop and @step < 0 return
	
	--ensure we don't overshoot
	set @stop = @stop - @step

	--treat negatives as unlimited
	set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

	--generate output
	;with myCTE (n,i) as 
	(
		--start at the beginning
		select @start
		, 1
		union all
		--increment in steps
		select n + @step
		, i + 1
		from myCTE 
		--ensure we've not overshot (accounting for direction of step)
		where (@maxResults=0 or i<@maxResults)
		and 
		(
			   (@step > 0 and n <= @stop)
			or (@step < 0 and n >= @stop)
		)  
	)
	insert @results
	select n 
	from myCTE
	option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

	--all good	
	return
	
end

Putting this to use for your scenario:

declare @start datetime = '2013-12-05 09:00'
	   ,@end  datetime = '2014-03-02 13:00'

--get dates (midnight)
--, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day
--, incrementing by 1 day
select CAST(n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default)

--get dates (start time)
--, incrementing by 1 day
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default)

--get dates (start time)
--, incrementing by 1 hour
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)

2005 Compatible

• Common Table Expressions: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• Option MaxRecursion Hint: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
• Table Valued Functions: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
• Default Parameters: http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
• DateTime: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
• Casting: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx

I like CTE as it's easy to read and maintenance

Declare @mod_date_from date =getdate();
Declare @mod_date_to date =dateadd(year,1,@mod_date_from);

with cte_Dates as (
			SELECT @mod_date_from as reqDate
			UNION ALL
			SELECT DATEADD(DAY,1,reqDate)
			FROM cte_Dates
			WHERE DATEADD(DAY,1,reqDate) < @mod_date_to
		)
		SELECT * FROM cte_Dates
		OPTION(MAXRECURSION 0);

Don't forget to set MAXRECURSION

create a temp table with integers from 0 to the difference between your two dates.

SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;

I use the following:

SELECT * FROM dbo.RangeDate(GETDATE(), DATEADD(d, 365, GETDATE()));

-- Generate a range of up to 65,536 contiguous DATES
CREATE FUNCTION dbo.RangeDate (   
    @date1 DATE = NULL
  , @date2 DATE = NULL
)   
RETURNS TABLE   
AS   
RETURN (
    SELECT D = DATEADD(d, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.RangeSmallInt(0, ABS(DATEDIFF(d, @date1, @date2))) A
);

-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
    @num1 BIGINT = NULL
  , @num2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    SELECT TOP (
               CASE
                   WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1
                   ELSE 0
               END
           )
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B
    WHERE ABS(@num1 - @num2) + 1 < 65537
);

It isn't all that different from many of the solutions proposed already but there are several things I like about it:

• No tables required
• Arguments can be passed in any order
• Limit of 65,536 dates is arbitrary and can easily be expanded by swapping to a function such as RangeInt

This one should work.

select Top 1000 DATEADD(d, ROW_NUMBER() OVER(ORDER BY Id),getdate()) from sysobjects

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 https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers for info on both options.

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).

The best answer is probably to use the CTE, but there is no guarantee you are able to use that. In my case, I had to insert this list inside an existing query created dinamically by a query generator...couldn't use CTE nor stored procedures.

So, the answer from Devio was really useful, but I had to modify it to work in my environment.

In case you don't have access to the master db, you may use another table in your database. As for the example before, the maximum date range is given by the number of rows inside the table choosen.

In my example tough, using the row_number, you can use tables without an actual int column.

declare @bd datetime --begin date
declare @ed datetime --end date

set @bd = GETDATE()-50
set @ed = GETDATE()+5

select 
DATEADD(dd, 0, DATEDIFF(dd, 0, Data)) --date format without time
from 
(
	select 
	(GETDATE()- DATEDIFF(dd,@bd,GETDATE())) --Filter on the begin date
	-1 + ROW_NUMBER() over (ORDER BY [here_a_field]) AS Data 
	from [Table_With_Lot_Of_Rows]
) a 
where Data < (@ed + 1) --filter on the end date

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

This will generate a list of dates for up to 10,000 days (27 years ish)

declare @startDateTime datetime = '2000-06-02 00:00:00';
declare @endDateTime datetime = '2028-06-02 23:59:59';


SELECT DATEADD(DAY, (Thousands+Hundreds+Tens+Units) , @startDateTime) D
FROM ( 
              SELECT 0 Thousands
              UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000
              UNION ALL SELECT 4000 UNION ALL SELECT 5000 UNION ALL SELECT 6000
              UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
       ) Thousands
       CROSS JOIN ( 
              SELECT 0 Hundreds
              UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
              UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600
              UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
       ) Hundreds
       CROSS JOIN ( 
              SELECT 0 Tens
              UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
              UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
              UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
       ) Tens 
       CROSS JOIN ( 
              SELECT 0 Units
              UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
              UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
              UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
       ) Units
WHERE
       DATEADD(DAY, (Thousands+Hundreds+Tens+Units), @startDateTime)  <= @endDateTime 
ORDER BY (Thousands+Hundreds+Tens+Units)