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.

6 Answers 6


One option is a recursive CTE:

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

WITH theDates AS
     (SELECT @StartDate as theDate
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
SELECT theDate, 1 as theValue
  FROM theDates

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

  • Oh, yes! I'd given thought to a "numbers" table approach (Scott Hodgin's answer). For very large numbers of days, it might perform better. And a nice reusable approach (John C's answer) is always good. This was just the simplest and most straight-forward answer I could think of.
    – RDFozz
    Commented Mar 3, 2017 at 17:14
  • 1
    Just remember - recursion has a default limit of 100 when you don't specify a MAXRECURSION - I hate for your SP to blow when wider date ranges are passed in :) Commented Mar 3, 2017 at 19:25
  • 2
    @Scott Hodgin You're right. Added the MAXRECURSION hint to the query to resolve.
    – RDFozz
    Commented Mar 3, 2017 at 20:42
  • This method has an intentional or unintentional behavior of including the next day of the EndDate value includes a time. If this behavior is undesirable, change the WHERE to DATEADD(DAY, 1, theDate) < @EndDate Commented Jun 27, 2018 at 16:36
  • 1
    @ChrisPorter - Excellent point! However, if you make it DATEADD(DAY, 1, theDate) < @EndDate, you don't get the end of the range when both datetime values have the same time component. I modified the answer appropriately, but using <= @EndDate. If you don't want the end of range value included regardless, then < @EndDate would indeed be correct.
    – RDFozz
    Commented Jun 27, 2018 at 17:22

Another option is to use a Table-Valued-Function. This approach is very fast, and offers a little more flexibility. You supply the Date/Time Range, DatePart and Increment. Also offers the advantage of including it in a CROSS APPLY

For Example

Select * from [dbo].[udf-Range-Date]('2017-03-05','2017-04-11','DD',1) 


RetSeq  RetVal
1   2017-03-05 00:00:00.000
2   2017-03-06 00:00:00.000
3   2017-03-07 00:00:00.000
4   2017-03-08 00:00:00.000
5   2017-03-09 00:00:00.000
36  2017-04-09 00:00:00.000
37  2017-04-10 00:00:00.000
38  2017-04-11 00:00:00.000

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
  • @RobV Very true. Learned a long time ago there is NEVER one true answer. Commented Mar 3, 2017 at 15:02
  • Thanks for the reply. Looks like there's several ways to do this :) The other guy answered my question in a way that precisely solved the posed desired output, but you're right yours seems like it's more flexible.
    – Rob V
    Commented Mar 3, 2017 at 15:16
  • @JohnCappelletti This is perfect for what I need to do, but I needed it to remove Weekends and Holidays...I did this by changing the last line to the following Where D<=@R2 AND DATEPART(Weekday, D) not in (1,7) AND D NOT IN (SELECT Holiday_Date FROM Holidays). Holidays is a table that contains holiday dates.
    – MattE
    Commented Mar 22, 2019 at 19:05
  • @MattE Well done! To exclude weekends and holidays, I would have done the same. :) Commented Mar 23, 2019 at 13:50
  • @JohnCappelletti - Nicely done, John! +1
    – Jeff Moden
    Commented May 3, 2022 at 23:15

Using Aaron Bertrand's post on how to create a date dimension table as an example, I came up with this:

DECLARE @StartDate DATE ='2017-03-05 00:00:00'
DECLARE @EndDate DATE ='2017-04-11 00:00:00'

Declare @DateTable table ([date]       DATE PRIMARY KEY);

-- use the catalog views to generate as many rows as we need

INSERT @DateTable ([date])
    SELECT d = DATEADD(DAY, rn - 1, @StartDate)
    FROM (
        SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)) rn = ROW_NUMBER() OVER (
                ORDER BY s1.[object_id]
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        -- on my system this would support > 5 million days
        ORDER BY s1.[object_id]
        ) AS x
    ) AS y;

FROM @DateTable
ORDER BY [date]

You should be able to put this type of logic in your stored procedure and add whatever else you need.


I had to solve a similar problem recently on Redshift where I only had read access and thus needed a purely SQL-based solution (no stored procedures) to getting a row for every hour in a date range as a starting point for my result set. I'm sure others can make this more elegant and modify it for their purposes, but for those in need, here is my hacked solution:

with hours as
   (select 0 clockhour union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12 
    union select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union select 19 union select 20 union select 21 union select 22 union select 23)
, days as
   (select *
       (select to_number(n0.number || n1.number, '99') daynum
           (select 0 as number union select 1 union select 2 union select 3) as n0
           cross join
           (select 1 as number union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) as n1)
    where daynum between 1 and 31)
, months as
   (select 1 as monthnum, 'jan' as themonth, 31 as numdays union select 2, 'feb', 28 union select 3, 'mar', 31 union select 4, 'apr', 30 union select 5, 'may', 31 union select 6, 'jun', 30 
    union select 7, 'jul', 31 union select 8, 'aug', 31 union select 9, 'sep', 30 union select 10, 'oct', 31 union select 11, 'nov', 30 union select 12, 'dec', 31)
, years as
   (select century || decade || yr as yr
       (select 19 century union select 20) 
    cross join
       (select 0 decade union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) 
    cross join
       (select 0 yr union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9))
select cast(daynum || '-' || themonth || '-' || yr || ' ' || clockhour || ':00:00' as timestamp) dayhour
from hours
cross join days
cross join months
cross join years
where cast(daynum || '-' || themonth || '-' || yr || ' ' || clockhour || ':00:00' as timestamp) 
between date_trunc('month', dateadd('month', -$MONTHS_AGO, getdate()))
and     date_trunc('month', dateadd('month', $MONTHS_AHEAD, getdate()))
and   daynum <= numdays
order by yr, monthnum, daynum, clockhour;

Another option is to insert the date records into the table using a loop.

The code below adds them to a temp table but this could easily be adapted to run in a stored proc with a permanent table.

DECLARE @StartDate DATE = '2020-01-01'
DECLARE @EndDate DATE = '2022-12-31'


SET @d = @StartDate
WHILE @d <= @EndDate BEGIN

    INSERT INTO #d (Date) VALUES (@d)

    SET @d = DATEADD(DAY, 1, @d)

  • While this will likely work, it's never a good idea to write procedural code for something that can be done with a single SQL statement.
    – mustaccio
    Commented Jul 8, 2022 at 16:05
  • Yes, there are a lot of beliefs about what's "never a good idea" in SQL Server :-D For me, using the iterative method is elegant, efficient, readable, performant, and maintainable. 5 out of 5. It's also shorter in terms of code length, for what that's worth. Any coder reading it in future years would easily understand what's going on. (By contrast, using cross joins is opaque and less readable, and a coder coming across it in future would probably struggle to understand what's going on, and why.) And, it's also future-proof in case MS decides to deprecate/change internal table names ;-)
    – Belladonna
    Commented Aug 15, 2022 at 12:22
  1. Fill about 10 records in Item & 3 in Category tables, then fill Ledger table with historical data from 01.01.10 to 31.12.20 as 10 records per day using a stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.