Date Range - Determine # days in date range for each month

[jasonhuibers]

I have a Date1 and a Date2. I need to break it down as the following expected outcome using SQL:

Date1 Date2
08/12/2012 10/7/2012

Result:
Month Num Days
Aug, 2012 19
Sept, 2012 30
Oct, 2012 7

 

[SkipVought]

So do you have a company date table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jasonhuibers]

No we do not have a company date table - would just like to plug in Date1 and Date2...

 

[djj55]

Here is something to consider.

declare @d1 date = '1/15/2013', @d2 date = getdate();
-- After checking the two are not in the same month
SELECT DATEDIFF(day, @d1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@d1)+1,0))), 
    DATEDIFF(day, DATEADD(month,DATEDIFF(month, 0,GETDATE()),0), @d2)

I am not sure about the months between

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[flapeyre]

Does this help?

SQL Server query for total number of days for a month between date ranges

-- Francis
Aut viam inveniam aut faciam.

 

[drlex]

With flapeyre's suggestion in mind, a numbers table is always a useful addition to a database for queries like this.
Have a read of this article:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

soi là, soi carré