Sql-server – How to create a row for every day in a date range using a stored procedure

datetimesql serverstored-procedurest-sql

I would like to create a stored procedure that will create a row in a table for every day in a given date range. The Stored Procedure accepts two inputs – A start date and end date of the date range desired by the user.

So, let's say I have a table like so:

SELECT Day, Currency
FROM ConversionTable

Day is a DateTime, and Currency is just an integer.

To keep things simple, let's just say I always want the Currency column to be 1 for each of these inserted rows. So, if someone inputs 'March 5, 2017' as the start date and 'April 11, 2017' as the end date, I would like the following rows created:

2017-03-05 00:00:00, 1
2017-03-06 00:00:00, 1
...
2017-04-11 00:00:00, 1

What's the best way to code the stored procedure to do this? I am using SQL Server 2008 R2 in my test environment, but our real environment uses SQL Server 2012, so I can upgrade my test machine if there is new functionality introduced in 2012 that makes this task easier.

Best Answer

One option is a recursive CTE:

DECLARE @StartDate datetime = '2017-03-05'
       ,@EndDate   datetime = '2017-04-11'
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

(MAXRECURSION hint added thanks to Scott Hodgin's comment, below.)

Related Question