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
asset 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);goRun 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.
