http://stackoverflow.com/questions/18869223/tsql-how-to-retrieve-the-last-date-of-each-month-between-given-date-range declare @tmpTable table (dates date) declare @startDate date = '01/01/2016' declare @endDate date = '12/31/2016' while @startDate <= @endDate begin insert into @tmpTable (dates) values (@startDate) set @startDate = DATEADD(DAY, 1, @startDate) end select max(dates) as [Last day],DATENAME(weekday,max(dates)) as [Last day] from @tmpTable as o group by datepart(YEAR, dates), datepart(MONTH, dates)
