Detect consecutive dates ranges using SQL

No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

WITH t AS (  SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i  FROM @d  GROUP BY InfoDate)SELECT MIN(d),MAX(d)FROM tGROUP BY DATEDIFF(day,i,d)

Here you go..

;WITH CTEDATESAS(    SELECT ROW_NUMBER() OVER (ORDER BY Infodate asc ) AS ROWNUMBER,infodate FROM YourTableName  ), CTEDATES1AS(   SELECT ROWNUMBER, infodate, 1 as groupid FROM CTEDATES WHERE ROWNUMBER=1   UNION ALL   SELECT a.ROWNUMBER, a.infodate,case datediff(d, b.infodate,a.infodate) when 1 then b.groupid else b.groupid+1 end as gap FROM CTEDATES A INNER JOIN CTEDATES1 B ON A.ROWNUMBER-1 = B.ROWNUMBER)select min(mydate) as startdate, max(infodate) as enddate from CTEDATES1 group by groupid

please don't forget to mark it as answer, if this answers your question.

I have inserted these values into a table called #consec and then perforemed the following:

select t1.*,t2.infodate as binfodinto #temp1from #consec t1left join #consec t2 on dateadd(DAY,1,t1.infodate)=t2.infodateselect t1.*,t2.infodate as binfodinto #temp2from #consec t1left join #consec t2 on dateadd(DAY,1,t2.infodate)=t1.infodate;with cte as(select infodate,  ROW_NUMBER() over(order by infodate asc) as seq from #temp1where binfod is null),cte2 as(select infodate, ROW_NUMBER() over(order by infodate asc) as seq from #temp2where binfod is null)select t2.infodate as [start_date],t1.infodate as [end_date] from cte t1left join cte2 t2 on t1.seq=t2.seq 

As long as your date periods are not overlapping, that should do the job for you.