Calculating number of full months between two dates in SQL

Question

I need to calculate the number of FULL month in SQL, i.e.

• 2009-04-16 to 2009-05-15 => 0 full month
• 2009-04-16 to 2009-05-16 => 1 full month
• 2009-04-16 to 2009-06-16 => 2 full months

I tried to use DATEDIFF, i.e.

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

but instead of giving me full months between the two date, it gives me the difference of the month part, i.e.

1

anyone know how to calculate the number of full months in SQL Server?

Answer 1

DATEDIFF() is designed to return the number boundaries crossed between the two dates for the span specified. To get it to do what you want, you need to make an additional adjustment to account for when the dates cross a boundary but don't complete the full span.

Answer 2

The original post had some bugs... so I re-wrote and packaged it as a UDF.

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

Answer 3

I googled over internet. And suggestion I found is to add +1 to the end.

Try do it like this:

Declare @Start DateTime
Declare @End DateTime

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

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

Answer 4

What's your definition of a month? Technically a month can be 28,29,30 or 31 days depending on the month and leap years.

It seems you're considering a month to be 30 days since in your example you disregarded that May has 31 days, so why not just do the following?

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

Answer 5

Try: trunc(Months_Between(date2, date1))

Answer 6

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

Works fine for me on SQL SERVER 2000

Answer 7

This is for ORACLE only and not for SQL-Server:

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

And for full month:

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

Can be used in Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g.