T-SQL Tuesday: Return a range of dates between 2 days

Just after Adam Machanic announced the first of the T-SQL Tuesday blog events, I was asked a question about how to return a list of all the dates between two given days.  I came across some good forum posts about how to do so, many of which utilised numbers tables.  I wanted to avoid that (nothing against them, just wanted something that was stand-alone), and figured that a CTE would be the best way to go; in fact someone had posted such a method, a little of which I have used here (if I can find the post again I’ll give them a mention in the comments!).

The following procedure takes 3 parameters: a StartDate, an EndDate and a bit flag to say if the range should be inclusive (that is, will it also return the StartDate and EndDate in the results), defaulted to 1.  A list of all the dates between the first two parameters will be returned.

The maxrecursion option has been set to 3660, giving a maximum of ten years dates to return.

create procedure dbo.GetDateRange
@StartDate datetime,
@EndDate datetime,
@Inclusive bit = 1
as

set nocount on;

declare @DayDiff smallint;
set @DayDiff = datediff(dd, @StartDate, @EndDate);
if @Inclusive = 0
set @DayDiff = @DayDiff – 1;

with cteRange (DateRange)
as (
select dateadd(dd, datediff(dd, 0, @EndDate) – @DayDiff, 0)
union all
select dateadd(dd, 1, DateRange)
from cteRange
where dateadd(dd, 1, DateRange) < (@EndDate + @Inclusive)
)
select DateRange
from cteRange
option (maxrecursion 3660);
go

Run the procedure using the following code:

exec dbo.GetDateRange '2009-12-07', '2009-12-12'

and the results are as shown in the image at the top of the post.

Hope some of you find this useful.  This post is part of Adam Machanic’s T-SQL Tuesday.

3 comments

  1. nick

    I love your posts. Learn a lot. Practical real world examples.

    so I was playing around with CTE and thought I’d make use of ROW_NUMBER(). It works, BUT I DO NOT KNOW WHY???? LOL

    Perhaps you can explain it? and maybe minimize it further?

    declare @a as datetime
    declare @b as datetime

    set @a = ‘2012-02-21′
    set @b = ‘2012-09-11′;

    with cte(Z,X,A) as
    (

    select ROW_NUMBER() OVER (ORDER BY @a) Z, 1 X, @a A
    union all
    SELECT ROW_NUMBER() OVER (ORDER BY A) Z,X,DateAdd(day,X,A) A from cte where A<@b

    )

    SELECT * FROM cte
    option (maxrecursion 3660);

  2. nick

    in fact, row number is not necessary at all

    declare @a as datetime
    declare @b as datetime

    set @a = ‘2012-02-21′
    set @b = ‘2012-09-11′;

    with cte(A) as
    (

    select @a A
    union all
    select DateAdd(day,1,A) A from cte where A<@b

    )

    SELECT * FROM cte
    option (maxrecursion 3660);

Leave a Reply