Generate a resultset of incrementing dates in TSQL

user_23199 0
Viewed 60476

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.

sql-server-2005 tsql sql-server sql database
17 Answers

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)
user_15797610
Related