t-sql get all dates between 2 dates
51,268
Solution 1
Assuming SQL Server 2005+, use a recursive query:
WITH sample AS (
SELECT CAST('2010-12-01' AS DATETIME) AS dt
UNION ALL
SELECT DATEADD(dd, 1, dt)
FROM sample s
WHERE DATEADD(dd, 1, dt) <= CAST('2010-12-04' AS DATETIME))
SELECT *
FROM sample
Returns:
dt
---------
2010-12-01 00:00:00.000
2010-12-02 00:00:00.000
2010-12-03 00:00:00.000
2010-12-04 00:00:00.000
Use CAST/CONVERT to format as you like.
Using parameters for start & end:
INSERT INTO dbo.YOUR_TABLE
(datetime_column)
WITH sample AS (
SELECT @start_date AS dt
UNION ALL
SELECT DATEADD(dd, 1, dt)
FROM sample s
WHERE DATEADD(dd, 1, dt) <= @end_date)
SELECT s.dt
FROM sample s
Solution 2
You need a numbers table. If you don't have a permanent one this is a more efficient way of generating one than using a recursive CTE. A permanent one will be more efficient though as long as it is read from the buffer cache.
DECLARE @D1 DATE = '2010-12-01'
DECLARE @D2 DATE = '2010-12-04'
;WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4)
SELECT DATEADD(day,i-1,@D1)
FROM Nums where i <= 1+DATEDIFF(day,@D1,@D2)
Solution 3
I just did something like this:
declare @dt datetime = '2010-12-01'
declare @dtEnd datetime = '2010-12-04'
WHILE (@dt < @dtEnd) BEGIN
insert into table(datefield)
values(@dt)
SET @dt = DATEADD(day, 1, @dt)
END
Solution 4
Repeated Question
Getting Dates between a range of dates
DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);
Related videos on Youtube
Comments
-
dcp over 3 years
Possible Duplicate:
Getting Dates between a range of datesLet's say I have 2 dates (date part only, no time) and I want to get all dates between these 2 dates inclusive and insert them in a table. Is there an easy way to do it with a SQL statement (i.e without looping)?
Ex: Date1: 2010-12-01 Date2: 2010-12-04 Table should have following dates: 2010-12-01, 2010-12-02, 2010-12-03, 2010-12-04
-
OMG Ponies over 13 yearsTSQL is SQL Server or Sybase, no Oracle/PLSQL supplied in there (that I saw) but Oracle didn't support the recursive WITH until 11gR2.
-
Martin Smith over 13 yearsSee this answer for performance benchmarks of recursive CTEs.
-
SDReyes over 13 yearsThanks! (editing...) btw they offer many solutions one of them is Oracle oriented :) +1
-
OMG Ponies over 13 years@Martin Smith: Interesting - KM and I compared about a year back, found the subtreecost for the example put the CTE barely ahead of the NUMBERS trick.
-
Martin Smith over 13 years@OMG - I found the same results as Jeff Moden when doing my own testing and looking at the actual cpu, reads, and duration. Doing
SELECT COUNT(*) FROM <Recursive CTE returning 1 million numbers>
took massively longer than doingSELECT COUNT(*) FROM <Cross Joined CTE returning 1 million numbers>
-
Martin Smith over 13 years
;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1),L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4 B),Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L5) SELECT COUNT(n) FROM Nums WHERE n<=1000000
takes less than 1 second. -
Martin Smith over 13 years
;WITH Nums AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM Nums WHERE n < 1000000) SELECT COUNT(n) FROM Nums WHERE n<=1000000 OPTION (MAXRECURSION 0);
takes > 20 seconds on my desktop. -
OMG Ponies over 13 years@Martin Smith: The recursive one took 15 seconds on our Dev box, waiting to see if the co-lo staff will call me about it =)
-
dcp over 13 yearsThanks, but one of the requirements was not to use a loop (see original question).
-
Zack Peterson about 12 years"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
-
Davos over 10 yearsThis should be marked as the answer, not the Recursive CTE. Recursion is a loop and loops are slower in SQL. Note that Itzik's original example of this technique uses TOP to restrict the cross joins from going too far. "Where i <=" is not going to stop the cross joins, see his article: sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-
Davos over 10 yearsChange the Select part to: SELECT TOP (1+DATEDIFF(day,@D1,@D2)) DATEADD(day,i-1,@D1) FROM Nums
-
Onaiggac almost 10 yearsIts better define dates without the '-' (ISO format YYYYMMDD). In my database this solution not worked.
-
Alexander Abakumov almost 10 yearsAdd
OPTION (MAXRECURSION 0)
to the end of the queries to avoidThe statement terminated. The maximum recursion 100 has been exhausted before statement completion
error. -
yonsk over 9 yearsLonger than first (accepted) solution above but more intuitive.
-
Steve Sether almost 9 yearsThis is by far the best answer, and doesn't suffer from recursion problems.
-
vCillusion about 6 yearsHi @martin-smith, Brilliant answer! Please explain the above logic in detail. It would be great to know it.
-
freedomn-m almost 6 years@vCillusion the explanation is on the sqlmag link. Essentially, the cross join unions create a bunch of
1
s (65536 of them, add another L5 to get more) and theROW_NUMBER()
then gives them an index. -
Greg Z. over 5 yearsRecursive is almost never a salable solution. See "Martin Smith" solution here taken from Itzik original idea. It scales well for any number of days and outperforms anything else except for having a physical table in place.