How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605

I need to get a list of Months and the total days in those months that fall between those two dates, like so:

March 28
April 30
May   31
June  03

Here's my attempt. Still think there are unanswered questions in your sample data, though.

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20120302',  @e = '20120605';

;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER 
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS 
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld) 
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

Results:

Month  Days
-----  ----
March    29
April    30
May      31
June      5

StackExchange Database Administrators Q#18626, answer score: 5