Sql-server – Convert list of dates in a date range in SQL Server

datesql server

Convert list of dates in a date range in SQL Server

For ex:

enter image description here

Best Answer

It's classic "islands" problem.

Here is "classic" solution:

declare @t table (dt date);

insert into @t(dt) values 
  ('20180202'), 
  ('20180203'), 
  ('20180204'), 
  ('20180205'), 
  ('20180209'), 
  ('20180212'), 
  ('20180213');

with c as 
( 
  select dt, dateadd(day, -1 * dense_rank() over(order by dt), dt) as grp 
  from @t 
) 
select min(dt) as start_range, max(dt) as end_range 
from c 
group by grp;
Related Question