# 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
``````
-
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

## 5 Answers

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
``````
-
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
select dateadd(day, 1, C.TheDate)
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
``````
-
Mikael Eriksson, can u please suggest me how i use your query in my rdlc reportrs, Actually i want to add number of rows in my rdlc report dynamically according to the number of days till current date. – user41314 Jun 19 '14 at 12:40
@Kamyasharma I made this a comment so you can ask him how to make this work from this answer. If he can't add an edit to this answer to explain that, then you will need to ask a new question as others explained to you before. – jcolebrand Jun 19 '14 at 17:03
@Kamyasharma I don't know what a rdlc report is and I don't understand what you want to do. I recommend you to ask a new question. – Mikael Eriksson Jun 19 '14 at 17:20

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

-

When I have problems like this, I use a calendar table. With a calendar table, your question can be answered by a simple query.

``````select month_of_year, count(*)
from calendar
where cal_date > '2012-03-02'
and cal_date < '2012-06-05'
group by month_of_year
order by month_of_year;

month_of_year  cal_date
--
3              29
4              30
5              31
6              4
``````
-

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
DATEADD
(
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;
``````
-

## protected by Paul White♦Jul 31 '15 at 16:39

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?