15,967,252 members
See more:
Hi friends

Iam having two dates
Example : 2007-10-01 and 2012-08-24

My requirement is I need number of months in each financial year between these two dates

(ie)
Financial Year Months
2007-2008 6
2008-2009 12
2009-2010 12
2010-2011 12
2011-2012 12
2012-2013 5

How to arrive this using sql query

Dear friends financial year should be from April to March
(ie) for 2007-2008 means 2007-04-01 to 2008-03-31
for 2008-2009 means 2008-04-01 to 2009-03-31
Posted
Updated 27-Aug-15 1:44am
v2
Maciej Los 27-Aug-15 6:45am
What have you tried?

## Solution 1

DateDiff[^] is what you're looking for.

To be able to calculate it for each year in date range, you have to use CTE[^]:
SQL
```DECLARE @startdate DATE = '2007-10-01'
DECLARE @enddate DATE = '2012-08-24'

;WITH MIFY AS
(
--initial values
SELECT  @startdate AS StartDate, DATEFROMPARTS(YEAR(@startDate), 12,31) AS EndOfYear, DATEDIFF(MM, @startdate, DATEFROMPARTS(YEAR(@startDate), 12,31)) +1 AS Months
WHERE @enddate> DATEFROMPARTS(YEAR(@startdate), 12, 31)
--recursive part
UNION ALL
FROM MIFY
UNION ALL
SELECT DATEADD(DD, 1,EndOfYear) AS StartDate, @enddate AS EndOfYear, DATEDIFF(MM, DATEADD(DD, 1,EndOfYear), @enddate) +1 AS Months
FROM MIFY
)
SELECT *
FROM MIFY```

Result:
```StartDate	EndOfYear	Months
2007-10-01	2007-12-31	3
2008-01-01	2008-12-31	12
2009-01-01	2009-12-31	12
2010-01-01	2010-12-31	12
2011-01-01	2011-12-31	12
2012-01-01	2012-08-24	8```

Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]

v2
Wendelius 28-Aug-15 0:01am
Nice!
Maciej Los 28-Aug-15 3:05am
Thank you, Mika.

## Solution 2

Well.....Original found here[^]
and with a bit playing:
SQL
```declare @dtFrom date
declare @dtTo date

select @dtFrom = '2007-10-01'
,@dtTo   = '2012-08-24'

select year(dt) [Year],   count(*) as Months
from (select top(datediff(MONTH, @dtFrom, @dtTo)) dateadd(MONTH,  row_number() over (order by (select null)), @dtFrom) dt
from sys.columns) q
group by year(dt)
order by [Year]
```

[EDIT]
Improved query:
SQL
```select year(dt) [Year],   count(*) as Months
from (
select top(datediff(MONTH, @dtFrom, @dtTo)+1) dateadd(MONTH,  row_number() over (order by (select null)) -1, @dtFrom) dt
from sys.columns
) q
group by year(dt)
order by [Year]```

[/EDIT]

v3
Maciej Los 27-Aug-15 7:18am
If i'm not wrong, this returns wrong result for first period of time (2 months instead of 3).
[EDIT]
I made some corrections to return proper resultset.
BTW: Very clever solution. +4 for link!