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
``````
-
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. –  Kamya sharma Jun 19 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 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 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

-

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

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