Date Diff- TSQL # months in each year between dates

Question

Problem: Time Span between two dates. I would like to know how many months are between each date. The trick is: the number of months in each year between the two dates.

For example: Start date = 1/1/2014 End Date = 3/1/2016

The output:

Column 1: "2014" would have a value of 12 Column 2: "2015" would have a value of 12 Column 3: "2016" would have a value of 2

This would be for a list with many dates (with different years)

EDIT: You would indeed have to have 14 year columns for a date span between 2000-2014. However, it is unlikely that more than 5 columns would need to be added.

Current train of thought

declare @datediff as int

select
@datediff=(Datediff(MONTH,[begin date], [end date]))
from [DateRange]
select 

case 
when  @datediff <= 12 then @datediff 
when @datediff <= 24 then @datediff -12 
when @datediff <= 36 then @datediff -24
when @datediff <= 48 then @datediff -36
else NULL 
end
from [DateRange]

Any ideas on this one?

I am very new to SQL and was only able to get the total months between the two with the following code:

select 
datediff(MONTH,[begin date], [end date]) 
from [tableofdates]

@Blam Yes, However it is highly doubtful that anymore than 5 columns would be required — Brian, Aug 08 '14 at 18:30
http://stackoverflow.com/questions/2054975/how-to-get-the-number-on-months-between-two-dates-in-sql-server-2005?rq=1 — Paul Williams, Aug 08 '14 at 19:36

score 1 · Accepted Answer · answered Aug 08 '14 at 19:42

Use below Query, you need to use your table in place of mydates table in below example. I used for maximum 10 year difference (represented by columns Y1,Y2 ... Y10).

The outer Query group by is used transpose the data to match to your requirement where you wanted month difference in column...

Inner query Q3 will provide the same results in rows with no limit to date range (actually there is limit i.e 2048 years due to master table master..spt_values which I guess you will not reach).

select 
Q3.begindt,
Q3.enddt,
Q3.Diff_in_Year,
sum(Case when Q3.Year_Counter = 0 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y1,
sum(Case when Q3.Year_Counter = 1 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y2,
sum(Case when Q3.Year_Counter = 2 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y3,
sum(Case when Q3.Year_Counter = 3 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y4,
sum(Case when Q3.Year_Counter = 4 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y5,
sum(Case when Q3.Year_Counter = 5 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y6,
sum(Case when Q3.Year_Counter = 6 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y7,
sum(Case when Q3.Year_Counter = 7 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y8,
sum(Case when Q3.Year_Counter = 8 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y9,
sum(Case when Q3.Year_Counter = 9 Then datediff(mm,Q3.y_start,Q3.y_end)+1 else 0 end) Y10
From
(select 
Q1.begindt,
Q1.enddt,
Q1.years Diff_in_Year,
Q2.number as Year_Counter,
(Case when Q2.number = 0 then Q1.begindt else dateadd(yy, datediff(yy,0,dateadd(yy,q2.number,q1.begindt)),0)End) AS y_Start,
(case when ((Q1.years-1) = Q2.number) then Q1.enddt else DATEADD(yy, DATEDIFF(yy,0,dateadd(yy,q2.number+1,q1.begindt) + 1), -1) End) AS y_End,
Year(Q1.begindt)+Q2.number YearInYYYY
from
 (select begindt,enddt,DATEDIFF(year,begindt,enddt)+1 as years from mydates) Q1
  join master..spt_values Q2 on Q2.type = 'P' and Q2.number < Q1.years
) Q3
Group by Q3.begindt,Q3.enddt,q3.Diff_in_Year

Output of the Above Query

begindt     enddt      YDif Y1  Y2  Y3  Y4  Y5  Y6  Y7  Y8  Y9  Y10
2010-07-02  2014-02-06 5    6   12  12  12  2   0   0   0   0   0
2011-01-01  2014-12-31 4    12  12  12  12  0   0   0   0   0   0
2012-05-22  2017-12-16 6    8   12  12  12  12  12  0   0   0   0

Very Good! Thank you so much. Looks quite elegant and something that I can work with!! — Brian, Aug 11 '14 at 13:58

Date Diff- TSQL # months in each year between dates

Current train of thought

1 Answers1