# Count the number of days in each month between two specified dates

I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:

• What if the start date is in November and the end date is in January? What if the range is longer than 12 months? What is the longest range in number of days? What if two people have the same vacation start/end? Don't you want some other identifier here? Commented Sep 11, 2019 at 19:49
• What if the start date is in November and the end date is in January ? Answer: It will count for next year in 2020. What is the longest range in number of days? Answer: Any
– boxi
Commented Sep 11, 2019 at 20:00
• Also under July you have a value of 4, I think this is a mistake, since none of your OOW-RTW ranges affect July. Commented Sep 11, 2019 at 20:36

This looks like a lot of code but I think it steps through and explains how it meets the requirements of counting days in the range, including leaving out days from the next year (in case someone forgets to filter for it first, which the sample usage also handles):

``````CREATE FUNCTION dbo.ReturnDateRangeByMonth
(
@start date, @end date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
WITH d(d) AS -- recursive to get all the days in the range
(
SELECT @start UNION ALL SELECT DATEADD(DAY, 1, d) FROM d
-- except the last day
-- and except any days from the next year
AND d < DATEFROMPARTS(YEAR(@start)+1, 1, 1)
-- on < 2012 use AND YEAR(d) = YEAR(@start)
),
m(m) AS -- grab all 12 months to pivot on
(
SELECT 1 UNION ALL SELECT m + 1 FROM m WHERE m < 12
)
SELECT m.m, c = COALESCE(COUNT(d.d),0) FROM m
LEFT OUTER JOIN d ON m.m = MONTH(d.d) GROUP BY m.m
);
``````

Sample usage:

``````DECLARE @vaca TABLE (OOW date, RTW date);

INSERT @vaca(OOW,RTW) VALUES('20191222','20200107'),('20190326','20190528'),
('20190328','20190522'),('20190612','20190617'),('20190404','20190418');

;WITH v AS
(
SELECT OOW, RTW FROM @vaca WHERE OOW >= '20190101' AND OOW < '20200101'
)
SELECT * FROM v CROSS APPLY dbo.ReturnDateRangeByMonth(v.OOW, v.RTW) AS f
PIVOT (MAX(c) FOR m IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
OPTION (MAXRECURSION 366);
``````

Results:

• Is the screen shot from SSMS? I've never seen a customization of the results pane like that.
– S3S
Commented Sep 11, 2019 at 20:41
• @scsimon No, Azure Data Studio. Commented Sep 11, 2019 at 20:43

Probably easiest to generate the dates between start and stop:

``````with t (d) as (
select cast('2017-02-10' as date)
union all
select dateadd(day, 1, d) from t where d < '2017-03-15'
)
select month(d), count(1) from t group by month(d);
``````

I think pivoting is better to do in the application layer, but you can do that if you insist:

``````with t (d) as (
select cast('2017-02-10' as date)
union all
select dateadd(day, 1, d) from t where d < '2017-03-15'
)
select count(case when month(d) = 1 then 1 end)
, count(case when month(d) = 2 then 1 end)
, count(case when month(d) = 3 then 1 end)
...
from t;
``````

EDIT: In the edited post there appear to be several intervals. You may consider creating a function for the date range. Example:

``````CREATE FUNCTION date_range(@start date, @stop date)
RETURNS TABLE AS
RETURN
with t (d) as (
select cast(@start as date)
union all
select dateadd(day, 1, d) from t where d < @stop
)
select d from t;

with t (x,y) as (
select '2017-02-10', '2017-03-15'
union all
select '2017-05-01', '2017-06-21'
)
select month(dr.d), count(1)
from t
cross apply date_range(t.x, t.y) dr
group by month(dr.d);
``````

If you want to count within each interval:

``````with t (x,y) as (
select '2017-02-10', '2017-03-15'
union all
select '2017-05-01', '2017-06-21'
)
select t.x, t.y, month(dr.d) as month, count(1) as cnt
from t
cross apply date_range(t.x, t.y) dr
group by t.x, t.y, month(dr.d);
``````