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.