Counts Days Per Month and Per Year b/w Two Dates

In  this blog we will learn how to retrieve number of days per month and per year b/w two dates.

 

Code: 

DECLARE @start DATETIME, @end DATETIME;

SELECT @start = '28-Oct-2015', @end = '05-May-2016';

select year(dt) [Year], Month(dt) [Month], count(*) [Numer_Of_Days]

from (select top(datediff(d, DATEADD(Day,-1, @start), @end)) dateadd(d, row_number() over (order by (select null)), DATEADD(Day,-1, @start)) dt

from sys.columns) q

group by year(dt), Month(dt)

order by [Year]

Result: