Count the number of days in each month between two specified dates

by boxi   Last Updated September 11, 2019 20:06 PM

I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:

enter image description here

Tags : sql-server


Answers 1


Probably easiest to generate the dates between start and stop:

with t (d) as ( 
    select cast('2017-02-10' as date) 
    union all 
    select dateadd(day, 1, d) from t where d < '2017-03-15' 
) 
select month(d), count(1) from t group by month(d);

I think pivoting is better to do in the application layer, but you can do that if you insist:

with t (d) as ( 
    select cast('2017-02-10' as date) 
    union all 
    select dateadd(day, 1, d) from t where d < '2017-03-15' 
) 
select count(case when month(d) = 1 then 1 end)
     , count(case when month(d) = 2 then 1 end) 
     , count(case when month(d) = 3 then 1 end) 
 ...
from t;
Lennart
Lennart
September 11, 2019 19:58 PM

Related Questions


creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM