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
@Inclusive bit = 1
set nocount on;
declare @DayDiff smallint;
set @DayDiff = datediff(dd, @StartDate, @EndDate);
if @Inclusive = 0
set @DayDiff = @DayDiff – 1;
with cteRange (DateRange)
select dateadd(dd, datediff(dd, 0, @EndDate) – @DayDiff, 0)
select dateadd(dd, 1, DateRange)
where dateadd(dd, 1, DateRange) < (@EndDate + @Inclusive)
option (maxrecursion 3660);
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.