how do I get the EXACT number of months between two dates?

Question

DATEDIFF(MONTH, '1/1/2014', '12/31/2014') + 1

This will give me 12.

What if I do this:

DATEDIFF(MONTH, '1/1/2014', '12/30/2014') + 1

It should give me 11 point something. How do I go about getting the exact number of months between these two dates? This needs to work for any combination of dates (any month of the year for any year).

Answer 1

You could do the calculation yourself in the following way:

DECLARE @startdate date = '1/1/2014'
DECLARE @enddate date = '12/30/2014'

DECLARE @startday int = DATEPART(DAY, @startdate)
DECLARE @endday int = DATEPART(DAY, @enddate)
DECLARE @startdateBase date = DATEADD(DAY, 1 - @startday, @startdate)
DECLARE @enddateBase date = DATEADD(DAY, 1 - @endday, @enddate)

DECLARE @deciMonthDiff float = CAST(DATEDIFF(MONTH, @startdate, @enddate) AS float) -
  (@startday - 1.0) / DATEDIFF(DAY, @startdateBase, DATEADD(MONTH, 1, @startdateBase)) +
  (@endday - 1.0) / DATEDIFF(DAY, @enddateBase, DATEADD(MONTH, 1, @enddateBase))

SELECT @deciMonthDiff

This calculates the @deciMonthDiff to be 11.935483870967.
Of course you can "inline" this as much as you want in order to avoid all the middle declarations.

The idea is to calculate the total month diff, then subtract the relative part of the first & last month depending on the actual day.

Answer 2

DATEDIFF with the MONTH option only returns an integer value. Using days or years would give you a rough "guesstimate" but still not exactly right (different number of days in a month/year so you can't just divide the days difference by 30).

If you want exact you would need to write your own function to walk through the months from start until end and account for how many days are in each month and get a percentage/factor of that month covered.

Answer 3

DECLARE @startdate date = '1/1/2014'
DECLARE @enddate date = '12/30/2014'

select  case when  DATEPART(DAY, @startdate) <=  DATEPART(DAY, @enddate) 
    then datediff(month, @startdate, @enddate)  
    else datediff(month, @startdate, @enddate) -1  
    end

from Chris The Jedi of T-SQL