How to determine the number of days in a month for a given Date Range?

I need to calculate using SQL Query, how many days within a given range fall into each calendar month.

I have given 2 dates, which define a date range; for example 2020-01-01 to 2020-08-03. I need to find how many days in that range fall in to each month i.e. how many fall into July, and how many into August.

In the example given, the expected result is 31 days in July and 3 days in August.

Answer

One approach uses a recusive query. Using date artithmetics, we can build the query so it performs one iteration per month rather than one per day, so this should be a rather efficient approach:

with cte as (
    select 
        datefromparts(year(@dt_start), month(@dt_start), 1) month_start,
        1 - day(@dt_start) + day(
            case when @dt_end > eomonth(@dt_start)
                then eomonth(@dt_start) 
                else @dt_end
            end
        ) as no_days
    union all
    select 
        dateadd(month, 1, month_start),
        case when @dt_end > dateadd(month, 2, month_start) 
            then day(eomonth(dateadd(month, 1, month_start)))
            else day(@dt_end)
        end
    from cte
    where dateadd(month, 1, month_start) <= @dt_end
)
select * from cte

Demo on DB Fiddle.

If we set the boundaries as follows:

declare @dt_start date = '2020-07-10';
declare @dt_end   date = '2020-09-10';

Then the query returns:

month_start | no_days
:---------- | ------:
2020-07-01  |      22
2020-08-01  |      31
2020-09-01  |      10