November 17, 2010 at 11:16 am
Hi All,
Say I have two dates and I want to find the number of days between them in each month
So for the following sample dates 2011-01-29 to 2011-02-05, I want to return the following.
Month Num of Day
1 3
24
Any ideas!!
November 17, 2010 at 1:13 pm
Remarks are in the code. Hope this helps.
DECLARE @StartDate datetime,
@EndDate datetime,
@days int;
SET @StartDate = '20110129';
SET @EndDate = '20110505';
-- get the number of days difference between the first of the month
-- of the starting and ending dates.
SET @days = DATEDIFF(day, DateAdd(month, DateDiff(month, 0, @StartDate), 0),
DateAdd(month, DateDiff(month, 0, @EndDate), 0));
-- See Jeff Moden's article
-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.
-- NOTE! A permanent tally table will always be MUCH faster
-- than this inline one. See the above article to create your own!
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
CTE (StartDate, EndDate) AS
(
-- get the starting date and the end of the starting month
SELECT @StartDate, DateAdd(day, -1, DATEADD(month, 1 + DateDiff(month, 0, @StartDate), 0)) UNION ALL
-- get the start of the ending month and the ending date
SELECT DATEADD(month, DateDiff(month, 0, @EndDate), 0), @EndDate UNION ALL
-- get the start and end of all months between
SELECT DATEADD(month, DateDiff(month, 0, @StartDate) + N, 0),
DATEADD(day, -1, DATEADD(month, 1+DateDiff(month, 0, @StartDate) + N, 0))
FROM Tally
WHERE N < DATEDIFF(MONTH, @StartDate, @EndDate)
)
-- if @days > 0, then specified dates cross at least one
-- month boundary, so get everything from the CTE
SELECT [Year] = YEAR(StartDate),
[Month] = MONTH(StartDate),
[Days] = DATEDIFF(day, StartDate, EndDate)+1
FROM CTE
WHERE @days > 0
UNION ALL
-- if @days = 0, then specified dates are in the same month
-- so get just this information.
SELECT YEAR(@StartDate),
MONTH(@StartDate),
DATEDIFF(day, @StartDate, @EndDate)+1
WHERE @days = 0
ORDER BY [Year], [Month];
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 18, 2010 at 9:38 am
That works perfectly!! Thanks for your help 🙂
November 18, 2010 at 5:26 pm
Thanks, glad it works for you (but hey... I did test it... would you really expect it to not work as desired?)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply