Years between dates, with "sticky" months

month_normalized_year_fraction.sql

CREATE FUNCTION sandbox.MonthNormalizedYearDiff(@StartDate DATE, @EndDate DATE)
RETURNS FLOAT
AS
BEGIN
    /*
    MONTH-NORMALIZED YEAR DIFF

    How many years are between 31 December 2015 and 31 January 2016, say?  You could say
    31 days in January / 366 days in the year 2016.  But notice how if I then try to
    "de-annualize" the resulting fraction, I can't multiply by 12, since 31/366 != 1/12.

    The solution is to "normalize" on the months, so each day has a slightly different weight
    depending on what month it’s in: days in months with 31 days have a lower weight (0.0027)
    than days in months with 30 days (0.0028), etc.

    Note: we are taking EOD @StartDate to EOD @EndDate, e.g. from 1 Dec 2015 to 2 Dec 2015
    is ONE day.
    */
    -- We will not allow @StartDate to be after @EndDate
    IF DATEDIFF(D, @StartDate, @EndDate) < 0
        RETURN NULL

    DECLARE @MonthsElapsed FLOAT
    DECLARE @StartMonthLength FLOAT
    DECLARE @EndMonthLength FLOAT

    -- Months between the dates.
    SET @MonthsElapsed = DATEDIFF(m, @StartDate, @EndDate) - 1
    -- The length of the month.  e.g. @StartDate in January means @StartMonthLength = 31.
    -- Avoid using the fancy EOMONTH(@StartDate, 0) function to keep compatibility with pre-2012 versions of T-SQL
    SET @StartMonthLength = DAY(DATEADD(month, ((YEAR(@StartDate) - 1900) * 12) + MONTH(@StartDate), -1))
    -- Same, but for the @EndDate
    SET @EndMonthLength = DAY(DATEADD(month, ((YEAR(@EndDate) - 1900) * 12) + MONTH(@EndDate), -1))

    -- Special case if the @StartDate and @EndDate are in the same month
    IF @MonthsElapsed < 0
        RETURN (DATEDIFF(d, @StartDate, @EndDate) / @EndMonthLength) / 12

    -- At least one month has passed, so we must calculate partial start and end months:
    DECLARE @StartMonthFraction FLOAT
    DECLARE @EndMonthFraction FLOAT

    -- This fraction will be zero if @StartDate is a month-end, and never 1, since if
    -- it was 1, @StartDate would be in the previous month to the month it is in; an absurity!
    SET @StartMonthFraction = (@StartMonthLength - DAY(@StartDate)) / @StartMonthLength
    -- This fraction will never be zero, but might be 1 if @EndDate is a month-end.
    SET @EndMonthFraction = DAY(@EndDate) / @EndMonthLength

    RETURN @MonthsElapsed / 12 + @StartMonthFraction / 12 + @EndMonthFraction / 12

END;
GO

SELECT sandbox.MonthNormalizedYearDiff('2015-12-31', '2016-01-31') -- returns 1/12
SELECT sandbox.MonthNormalizedYearDiff('2015-12-31', '2016-06-30') -- returns 0.5
SELECT sandbox.MonthNormalizedYearDiff('2015-12-31', '2016-12-31') -- returns 1

DROP FUNCTION sandbox.MonthNormalizedYearDiff