Generate a result set in ascending dates in TSQL

Question

Consider the need to create a set of dates. We have start and end dates, and we would like to create a list of dates between them.

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 using the 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 fall within a custom range using T-SQL? Assume SQL 2005+. If your answer uses SQL 2008 features, check as such.

Answer 1

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 distinct number from master.dbo.spt_values where name is null ) n where dateadd(day, number, @dt) < @dtEnd 

Answer 2

The following case 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. 

Answer 3

For this method to work, you need to complete this setup at once:

 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 configured, use this query:

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

to fix them:

 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 

exit:

 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) 

Answer 4

@KM responds by first creating a table of numbers and using it to select a date range. To do the same without a temporary number 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 ; 

Of course, if you do this often, a persistent table may be more efficient.

The above query is a modified version in this article , which discusses the generation of sequences and provides many possible methods. I liked this one because it does not create a temporary table and is not limited by the number of elements in the sys.objects table.

Answer 5

Try it. No Looping, CTE restrictions, etc., And you can have almost any not. created records. Manage cross-connect and vertex depending on 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 that the attachment is intended to simplify management and conversion to views, etc.

Answer 6

Another option is to create the corresponding function in .NET. Here's what 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 much smarter, but it illustrates the idea. In my experience, for small or medium time intervals (for example, after a couple of years) this function works better than that implemented in T-SQL. Another nice feature of the CLR version is that it does not create a temporary table.

Answer 7

Overview

Here is my version (compatible with 2005). The advantages of this approach are as follows:

• You get a general purpose function that you can use for a number of similar scenarios not limited to dates only
• the range is not limited by the contents of the existing table
• You can easily change the gain (for example, get a date every 7 days, not every day).
• you do not need access to other directories (i.e. wizard)
• sql engine capable of doing some TVF optimization which cannot with while expression
• generate_series is used in some other dbs, so this can help make your code instinctively familiar with a wider audience.

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

the code

The reusable function to create a range of numbers based on the 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 (ie 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 

Put this for use in 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 Compatibility

• General table expressions: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
• MaxRecursion Option Hint: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
• Table Values: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
• Default options: 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

Answer 8

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

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

Answer 9

I am using 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, AN, 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 ); 

This is not all that differs from the many solutions offered, but there are a few things that I like:

• No need for tables
• Arguments can be passed in any order.
• The limit of 65,536 dates is arbitrary and can be easily extended by replacing with a function such as RangeInt

Answer 10

This solution is based on the amazing answer of the same question for MySQL. This also works very well in MSSQL. stack overflow

 select DateGenerator.DateValue from ( select DATEADD(day, - (aa + (10 * ba) + (100 * ca) + (1000 * da)), CONVERT(DATE, GETDATE()) ) as DateValue from (select aa from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a cross join (select ba from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b cross join (select ca from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c cross join (select da 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 

only works for dates in the past, for dates in a future change minus the sign in the DATEADD function. The query only works for SQL Server 2008+, but can also be rewritten in 2005, replacing the "select from values" construct with union.

Answer 11

I would recommend: create an auxiliary table of numbers and use it to create a list of dates. You can also use a recursive CTE, but this may not work, and also join a helper table of numbers. See SQL, Auxiliary Table of Numbers for information on all parameters.

Answer 12

While I really like the KM solution above (+1), I have to question your “no loop” assumption - given the likely date ranges your application will work with, and the loop doesn't really have to be that expensive. The main trick is to split the results of the loop in the intermediate / cache table, so that extremely large sets of queries do not slow down the system by recounting the exact same dates. For example. each request only calculates / caches date ranges that are NOT already in the cache and what they need (and pre-populates a table with some realistic date range, for example, 2 years in advance, with a range determined by the needs of your application).

Answer 13

The best answer is probably to use CTE, but there is no guarantee that you can use it. In my case, I had to insert this list into an existing query created dynamically by the query generator ... could not use CTE or stored procedures.

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

If you do not have access to master db, you can use another table in your database. As in the previous example, the maximum date range is determined by the number of rows inside the selected table.

In my example, hard, using row_number, you can use tables without the 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 

Answer 14

Actually, as a Devio solution, since I needed something like this that needed to be run on SQL Server 2000 (so CTE cannot be used), how could it be modified to ONLY generate dates that match the given set days of the week, For example, I only need dates that coincide with Monday, Wednesday, and Friday, or any particular sequence I choose based on the next 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 do is add two additional fields: day, day_code Then filter the generated list with the 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 

Answer 15

I like CTE as it is easy to read and maintain.

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

Do not forget to install MAXRECURSION

Answer 16

That should work.

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