Generate a resultset of incrementing dates in TSQL
Solution 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 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.
Solution 2
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.
Solution 3
@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.
Solution 4
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.
Solution 5
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)
p.campbell
Developer in the Microsoft .NET & SQL Server stack. I am focused on delivering great applications in small iterations. I've developed solutions in marketing, healthcare, manufacturing, and transportation verticals. My open source projects on GitHub. Continuously learning.
Updated on March 11, 2020Comments
-
p.campbell about 4 years
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.