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

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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;