I am trying to get a list of all DateRanges of a given interval between two dates.
For example if I have the dates 2015-04-01 and 2015-06-20 and when the interval is 20 the result should be
DateFrom DateTo ------------------------ 2015-04-01 2015-04-21 2015-04-22 2015-05-12 2015-05-13 2015-06-02 2015-06-03 2015-06-20 -------------------
Here is the query I am trying
DECLARE @StartDateTime DATETIME DECLARE @EndDateTime DATETIME DECLARE @Interval int = 20 SET @StartDateTime = '2015-04-01' SET @EndDateTime = '2015-06-20'; WITH DateRange(DateStart,DateEnd) AS ( SELECT @StartDateTime, DATEADD(d,@Interval,@StartDateTime) UNION ALL SELECT DATEADD(d,1,DateEnd), DATEADD(d,@Interval,DateEnd) FROM DateRange WHERE DateEnd <= @EndDateTime ) SELECT CAST(DateStart as date) DateStart , CAST(DateEnd as Date) DateEnd FROM DateRange OPTION (MAXRECURSION 0) GO
Its returning the below result which is not exactly as my expected result above
DateStart DateEnd 2015-04-01 2015-04-21 2015-04-22 2015-05-11 2015-05-12 2015-05-31 2015-06-01 2015-06-20 2015-06-21 2015-07-10
Here as you can see, there is an extra row outside the given dates and also interval between second row onward is 19 days, not 20. I understand its because I am adding a Day to the first field after union all
Please help me to fix this query or please suggest a better way to implement the same.
Advertisement
Answer
Changes to your recursive cte should be
use CASE
to check for ending date. If it is greater than the required end date, set it to @EndDateTime
Also the WHERE
condition should be
WHERE DateEnd < @EndDateTime WITH DateRange(DateStart,DateEnd) AS ( SELECT @StartDateTime,DATEADD(day, @Interval, @StartDateTime) UNION ALL SELECT dateadd(day, 1, DateEnd), case when DATEADD(day, @Interval + 1, DateEnd) <= @EndDateTime then DATEADD(day, @Interval + 1, DateEnd) else @EndDateTime end FROM DateRange WHERE DateEnd < @EndDateTime )