一种方法使用递归查询。使用日期算法，我们可以构建查询，使其每月执行一次迭代，而不是每天执行一次，因此这应该是一种相当有效的方法：

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

在db小提琴上演示。
如果我们按以下方式设置边界：

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

然后查询返回：

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

你可以参考这个

;with dates(thedate) as (
  select dateadd(yy,years.number,0)+days.number
    from master..spt_values years
    join master..spt_values days
      on days.type='p' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
   where years.type='p' and years.number between 100 and 150
      -- note: 100-150 creates dates in the year range 2000-2050
      --       adjust as required
)
  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
   where d.thedate between '2020-01-01' and '2020-08-03'
group by datediff(m, 0, d.thedate)
order by themonth;

请参考下面的链接，richardthekiwi用户为您的场景提供了一个清晰的示例。
sql server查询日期范围之间一个月的总天数

你可以在月级而不是日级完成所有的工作，这应该快一点。下面是一个使用递归cte的方法：

with cte as (
      select @startdate as startdate, @enddate as enddate,
             datefromparts(year(@startdate), month(@startdate), 1) as month
      union all
      select startdate, enddate, dateadd(month, 1, month)
      from cte
      where dateadd(month, 1, month) < @enddate
     )
select month,
       (case when month <= startdate and dateadd(month, 1, month) >= enddate
             then day(enddate) - day(startdate) + 1
             when month <= startdate
             then day(eomonth(month)) - day(startdate) + 1
             when dateadd(month, 1, month) < enddate
             then day(eomonth(month))
             when dateadd(month, 1, month) >= enddate
             then day(enddate)
        end)
from cte;

还有那把小提琴。
在白天，逻辑更简单：

with cte as (
      select @startdate as dte, @enddate as enddate
      union all
      select dateadd(day, 1, dte), enddate
      from cte
      where dte < enddate
     )
select datefromparts(year(dte), month(dte), 1) as yyyymm, count(*)
from cte
group by datefromparts(year(dte), month(dte), 1) 
order by yyyymm
option (maxrecursion 0)

下面是一个递归cte的解决方案。

declare @startDate date = '2020-07-01'
declare @endDate   date = '2020-08-03'

; WITH cte (n, year, month, daycnt) 
AS (
    SELECT 
        0
        , DATEPART(year,         @startDate)
        , DATENAME(MONTH,        @startDate)
        , DATEPART(day, EOMONTH( @startDate ) ) - DATEPART(day, @startDate ) + 1
    UNION ALL
    SELECT    
        n + 1
        , DATEPART(year,         DATEADD(month, n + 1, @startDate) )
        , DATENAME(MONTH,        DATEADD(month, n + 1, @startDate) ) 
        , IIF(
            n = ( DATEPART(month, @endDate) - DATEPART(month, @startDate) ) + ( DATEPART(year, @endDate) - DATEPART(year, @startDate) ) * 12 - 1
            , DATEPART(day, @endDate ) 
            , DATEPART(day, EOMONTH( DATEADD(month, n + 1, @startDate) ) )
        )
    FROM    
        cte
    WHERE 
        n <= ( DATEPART(month, @endDate) - DATEPART(month, @startDate) ) + ( DATEPART(year, @endDate) - DATEPART(year, @startDate) ) * 12 - 1
)
SELECT *
FROM cte
ORDER BY n
OPTION (maxrecursion 0)

这可以用一个数字函数进一步简化，但它本质上也是一个递归的cte，尽管它看起来更干净。但是它需要在这个select语句之上定义一个函数。