SELECT DATEDIFF(MONTH, '2009-04-16', '2009-05-15')

1

CREATE FUNCTION FullMonthsSeparation
(
    @DateA DATETIME,
    @DateB DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT

    DECLARE @DateX DATETIME
    DECLARE @DateY DATETIME

    IF(@DateA < @DateB)
    BEGIN
        SET @DateX = @DateA
        SET @DateY = @DateB
    END
    ELSE
    BEGIN
        SET @DateX = @DateB
        SET @DateY = @DateA
    END

    SET @Result = (
                    SELECT
                    CASE
                        WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY)
                        THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
                        ELSE DATEDIFF(MONTH, @DateX, @DateY)
                    END
                    )

    RETURN @Result
END
GO

SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-15') as MonthSep -- =0
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-05-16') as MonthSep -- =1
SELECT dbo.FullMonthsSeparation('2009-04-16', '2009-06-16') as MonthSep -- =2

select case when DATEPART(D,End_dATE) >=DATEPART(D,sTAR_dATE)
THEN ( case when DATEPART(M,End_dATE) = DATEPART(M,sTAR_dATE) AND DATEPART(YYYY,End_dATE) = DATEPART(YYYY,sTAR_dATE)
        THEN 0 ELSE DATEDIFF(M,sTAR_dATE,End_dATE)END )
ELSE DATEDIFF(M,sTAR_dATE,End_dATE)-1 END

months_between(to_date ('2009/05/15', 'yyyy/mm/dd'),
               to_date ('2009/04/16', 'yyyy/mm/dd'))

round(months_between(to_date ('2009/05/15', 'yyyy/mm/dd'),
                     to_date ('2009/04/16', 'yyyy/mm/dd')))

datediff(month, dateadd(day,-day(startDate)+1,start),dateadd(day,-day(startDate)+1,endDate))

SELECT DATEDIFF(DAY, '2009-04-16', '2009-05-15')/30
    , DATEDIFF(DAY, '2009-04-16', '2009-05-16')/30
    , DATEDIFF(DAY, '2009-04-16', '2009-06-16')/30

trunc(Months_Between(date2, date1))

SELECT 12 * (YEAR(end_date) - YEAR(start_date)) +
    ((MONTH(end_date) - MONTH(start_date))) +
    SIGN(DAY(end_date) / DAY(start_date));

WITH
-- Count how many months must be added to @StartDate to exceed @DueDate
MONTHS_SINCE(n, [Month_hence], [IsFull], [RemainingDays] ) AS (
SELECT
    1 as n,
    DATEADD(Day, -1, DATEADD(Month, 1, @StartDate)) AS Month_hence
    ,CASE WHEN (DATEADD(Day, -1, DATEADD(Month, 1, @StartDate)) <= @LastDueDate)
        THEN 1
        ELSE 0
    END  AS [IsFull]
    ,DATEDIFF(day, @StartDate,  @LastDueDate) as [RemainingDays]
UNION ALL
SELECT
    n+1,
    --DateAdd(Month, 1, Month_hence) as Month_hence -- No, causes propagation of short month discounted days
    DATEADD(Day, -1, DATEADD(Month, n+1, @StartDate)) as Month_hence
    ,CASE WHEN (DATEADD(Day, -1, DATEADD(Month, n+1, @StartDate)) <= @LastDueDate)
        THEN 1
        ELSE 0
    END  AS [IsFull]
    ,DATEDIFF(day, DATEADD(Day, -1, DATEADD(Month, n, @StartDate)),  @LastDueDate)
    FROM MONTHS_SINCE
    WHERE Month_hence<( @LastDueDate --WHERE Period= 1
    )
), --SELECT * FROM MONTHS_SINCE
MONTH_TALLY (full_months_over_all_terms, months_over_all_terms, days_in_incomplete_month ) AS (
SELECT
    COALESCE((SELECT MAX(n) FROM MONTHS_SINCE WHERE isFull = 1),1) as full_months_over_all_terms,
    (SELECT MAX(n) FROM MONTHS_SINCE ) as months_over_all_terms,
    COALESCE((SELECT [RemainingDays] FROM MONTHS_SINCE WHERE isFull = 0),0) as days_in_incomplete_month
) SELECT * FROM MONTH_TALLY;

Select Name,
(SELECT CASE WHEN
DATEPART(DAY, '2016-08-28') > DATEPART(DAY, '2016-09-29')
THEN DATEDIFF(MONTH, '2016-08-28',  '2016-09-29') - 1
ELSE DATEDIFF(MONTH, '2016-08-28',  '2016-09-29') END) as NumberOfMonths

FROM
tableExample;

DECLARE @Time1 DATETIME
SET @Time1 = '12/14/2015'

DECLARE @Time2 DATETIME
SET @Time2 = '12/15/2016'

SELECT
Linear_YearMonths = (centuries.century + decades.decade + years.[year] + months.[Month]),
1 AS value
INTO #linear_months
FROM
(SELECT '18' [century] UNION ALL
SELECT '19' UNION ALL
SELECT '20') centuries
CROSS JOIN
(SELECT '0' [decade] UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9') decades
CROSS JOIN
(SELECT '1' [year] UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' UNION ALL
SELECT '0') years
CROSS JOIN
(SELECT '-01' [month] UNION ALL
SELECT '-02' UNION ALL
SELECT '-03' UNION ALL
SELECT '-04' UNION ALL
SELECT '-05' UNION ALL
SELECT '-06' UNION ALL
SELECT '-07' UNION ALL
SELECT '-08' UNION ALL
SELECT '-09' UNION ALL
SELECT '-10' UNION ALL
SELECT '-11' UNION ALL
SELECT '-12') [months]
ORDER BY 1

SELECT
Time1 = @Time1,
[YYYY-MM of Time1] = CASE
WHEN LEFT(MONTH(@Time1),1) <> '1' OR MONTH(@Time1) = '1'
    THEN (CAST(YEAR(@Time1) AS VARCHAR) + '-' + '0' + CAST(MONTH(@Time1) AS VARCHAR))
    ELSE (CAST(YEAR(@Time1) AS VARCHAR) + '-' + CAST(MONTH(@Time1) AS VARCHAR))
    END,
Time2 = @Time2,
[YYYY-MM of Time2] = CASE
WHEN LEFT(MONTH(@Time2),1) <> '1' OR MONTH(@Time2) = '1'
    THEN (CAST(YEAR(@Time2) AS VARCHAR) + '-' + '0' + CAST(MONTH(@Time2) AS VARCHAR))
    ELSE (CAST(YEAR(@Time2) AS VARCHAR) + '-' + CAST(MONTH(@Time2) AS VARCHAR))
    END
INTO #datepoints

SELECT
d.*,
Months_Between = (SELECT (SUM(l.value) - 1) FROM #linear_months l
            WHERE l.[Linear_YearMonths] BETWEEN d.[YYYY-MM of Time1] AND d.[YYYY-MM of Time2])
FROM #datepoints d

SELECT
d.*,
Months_Between = (SELECT (SUM(l.value) - 1) FROM AZ_VBP.[MY].[edg_Linear_YearMonths] l
            WHERE l.[Linear_YearMonths] BETWEEN d.[YYYY-MM of Time1] AND d.[YYYY-MM of Time2])
        + (CASE WHEN DAY(Time1) < DAY(Time2)
                THEN -1
                ELSE 0
                END)
FROM #datepoints d

select CAST(DATEDIFF(MONTH, StartDate, EndDate) AS float) -
  (DATEPART(dd,StartDate) - 1.0) / DATEDIFF(DAY, StartDate, DATEADD(MONTH, 1, StartDate)) +
  (DATEPART(dd,EndDate)*1.0 ) / DATEDIFF(DAY, EndDate, DATEADD(MONTH, 1, EndDate))

DECLARE @First date = '2015-08-31'
DECLARE @Last date = '2016-02-28'

SELECT
    @First as [First],
    @Last as [Last],
    DateDiff(Month, @First, @Last) as [DateDiff Thinks],
    CASE
        WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) +1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) +1
        WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) , @First) <= @Last Then DATEDIFF(Month, @First, @Last)
        WHEN DATEADD(Month, DATEDIFF(Month, @First, @Last) -1, @First) <= @Last Then DATEDIFF(Month, @First, @Last) -1
    END as [Actual Months Apart]

Declare @Start DateTime
Declare @End DateTime

Set @Start = '11/1/07'
Set @End = '2/29/08'

Select DateDiff(Month, @Start, @End + 1)