# 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
``````
-
What have you tried so far? – BryceAtNetwork23 May 31 '12 at 13:02
Do you not include the first day in the range, or the last two days in the range? I count 29 days from March 2 - March 31, and 5 days from June 1 to June 5. Can you please clarify? – Aaron Bertrand May 31 '12 at 13:48

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
(
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
``````
-
 Check this value @s = '20120331' – msi77 Jun 1 '12 at 6:07

You can use a recursive CTE to build a list of dates and group by month. This will not give you the exact output you need but it should give you an idea of what you can do.

``````declare @D1 date;
declare @D2 date;

set @D1 = '20120302';
set @D2 = '20120605';

with C(TheDate) as
(
select @D1
union all
from C
where C.TheDate < @D2
)
select cast(dateadd(month, datediff(month, 0, C.TheDate), 0) as date) as Month,
count(*) as DayCount
from C
group by dateadd(month, datediff(month, 0, C.TheDate), 0)
option (maxrecursion 0);
``````

Result:

``````Month      DayCount
---------- -----------
2012-03-01 30
2012-04-01 30
2012-05-01 31
2012-06-01 5
``````
-

This should work too.

``````;with a
as
(
select CAST('20120302' as DATetime) + 1 as d

union all
select DATEADD(day , 1,d) from a where DATEADD(day , 1,d) < CAST('20120605' as   DATetime)
)

select datename(month , d) as MonthName , count(1) as NUmDays from a
group by datename(month , d),datepart(month , d) OPTION (MAXRECURSION 0);
``````

--njds

-

Because I enjoy these types of questions, I will throw a solution I use often into the mix.

``````DECLARE
@StartDate DATETIME,
@EndDate DATETIME;

SELECT
@StartDate = CONVERT(DATETIME, '2012-03-02T00:00:00.000', 126),
@EndDate = CONVERT(DATETIME, '2012-06-05T00:00:00.000', 126);

WITH e1
(
i
)
AS
(
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
),
e2
(
i
)
AS
(
SELECT
1
FROM e1 AS a

CROSS JOIN e1 AS b
),
e4
(
i
)
AS
(
SELECT
1
FROM e2 AS a

CROSS JOIN e2 AS b
),
RawDates
(
RawDate
)
AS
(
SELECT
(
dd,
ROW_NUMBER()
OVER
(
ORDER BY
e.i ASC
) - 1,
@StartDate
) AS RawDate
FROM e4 AS e
),
FilteredDates
(
FilteredDate
)
AS
(
SELECT
r.RawDate
FROM RawDates AS r

WHERE r.RawDate BETWEEN @StartDate AND @EndDate
)
SELECT
CONVERT(VARCHAR(4), YEAR(f.FilteredDate)) + '-' + CONVERT(VARCHAR(2), MONTH(f.FilteredDate)) AS MonthString,
COUNT(1) AS TotalDays
FROM FilteredDates AS f

GROUP BY
CONVERT(VARCHAR(4), YEAR(f.FilteredDate)) + '-' + CONVERT(VARCHAR(2), MONTH(f.FilteredDate))

ORDER BY
MonthString ASC;
``````
-