I come to you because I have searched and I have not found anything that can help me with the problem I have. I must show in a query in SQL Server how many days of each month there are between two dates but grouped by months (I have an Initial date and a Final date), I explain with an example:
Start Date = 2017-08-17
Final Date = 2017-10-20
should show something like
August - > 15
September - > 30
October - > 20
I appreciate any help.
An easy way is by using a date table. If you do not have one, you can simulate it in different ways. In this case, I'm going to use the master.dbo.spt_values table (which has 2048 different numbers, so if you use a period of time longer than those days you would have to modify the code):
DECLARE @FechaInicial DATE = '20170817', @FechaFinal DATE = '20171020';
SELECT DATENAME(MONTH,DATEADD(DAY,number,@FechaInicial)) Mes,
COUNT(*) Dias
FROM master.dbo.spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@FechaInicial) <= @FechaFinal
GROUP BY DATENAME(MONTH,DATEADD(DAY,number,@FechaInicial)),
DATEPART(MONTH,DATEADD(DAY,number,@FechaInicial))
ORDER BY DATEPART(MONTH,DATEADD(DAY,number,@FechaInicial))
;
Here is a demo of this.
The result is:
╔═══════════╦══════╗
║ Mes ║ Dias ║
╠═══════════╬══════╣
║ August ║ 15 ║
║ September ║ 30 ║
║ October ║ 20 ║
╚═══════════╩══════╝
Hello! I found a code that can serve you, unfortunately I do not have SQL Server on hand to test it, but from what I see it should be:
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;
The code I got from here: link
There are other answers there that can also help you.
Greetings and success!