Calculating number of full months between two dates in SQL

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?

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

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

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

The dateadd function can be used to offset to the beginning of the month. If the endDate has a day part less than startDate, it will get pushed to the previous month, thus datediff will give the correct number of months.

DATEDIFF(MONTH, DATEADD(DAY,-DAY(startDate)+1,startDate),DATEADD(DAY,-DAY(startDate)+1,endDate))

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 and above.

I know this is an old question, but as long as the dates are >= 01-Jan-1753 I use:

DATEDIFF(MONTH, DATEADD(DAY,-DAY(@Start)+1,@Start),DATEADD(DAY,-DAY(@Start)+1,@End))

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.

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;   

Is not necesary to create the function only the @result part. For example:

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;

This answer follows T-SQL format. I conceptualize this problem as one of a linear-time distance between two date points in datetime format, call them Time1 and Time2; Time1 should be aligned to the 'older in time' value you are dealing with (say a Birth date or a widget Creation date or a journey Start date) and Time2 should be aligned with the 'newer in time' value (say a snapshot date or a widget completion date or a journey checkpoint-reached date).

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

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

The solution leverages simple measurement, conversion and calculations of the serial intersections of multiple cycles of different lengths; here: Century,Decade,Year,Month,Day (Thanks Mayan Calendar for the concept!). A quick note of thanks: I thank other contributors to Stack Overflow for showing me some of the component functions in this process that I've stitched together. I've positively rated these in my time on this forum.

First, construct a horizon that is the linear set of the intersections of the Century,Decade,Year,Month cycles, incremental by month. Use the cross join Cartesian function for this. (Think of this as creating the cloth from which we will cut a length between two 'yyyy-mm' points in order to measure distance):

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

Then, convert your Time1 and Time2 date points into the 'yyyy-mm' format (Think of these as the coordinate cut points on the whole cloth). Retain the original datetime versions of the points as well:

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

Then, Select the ordinal distance of 'yyyy-mm' units, less one to convert to cardinal distance (i.e. cut a piece of cloth from the whole cloth at the identified cut points and get its raw measurement):

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

Raw Output: I call this a 'raw distance' because the month component of the 'yyyy-mm' cardinal distance may be one too many; the day cycle components within the month need to be compared to see if this last month value should count. In this example specifically, the raw output distance is '12'. But this wrong as 12/14 is before 12/15, so therefore only 11 full months have lapsed--its just one day shy of lapsing through the 12th month. We therefore have to bring in the intra-month day cycle to get to a final answer. Insert a 'month,day' position comparison between the to determine if the latest date point month counts nominally, or not:

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

Final Output: The correct answer of '11' is now our output. And so, I hope this helps. Thanks!

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))

I realize this is an old post, but I created this interesting solution that I think is easy to implement using a CASE statement.

Estimate the difference using DATEDIFF, and then test the months before and after using DATEADD to find the best date. This assumes Jan 31 to Feb 28 is 1 month (because it is).

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]

SIMPLE AND EASY WAY, Just Copy and Paste this FULL code to MS SQL and Execute :

declare @StartDate date='2019-01-31'
declare @EndDate date='2019-02-28'


SELECT

DATEDIFF(MONTH, @StartDate, @EndDate)+

(

case 

when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate) AND DATEPART(DAY,@EndDate) = DATEPART(DAY,EOMONTH(@EndDate)) then 0

when format(@StartDate,'yyyy-MM') != format(@EndDate,'yyyy-MM') AND DATEPART(DAY,@StartDate) > DATEPART(DAY,@EndDate)  then -1 

else 0 

end

) 

as NumberOfMonths

All you need to do is deduct the additional month if the end date has not yet passed the day of the month in the start date.

DECLARE @StartDate AS DATE = '2019-07-17'
DECLARE @EndDate AS DATE = '2019-09-15'


DECLARE @MonthDiff AS INT = DATEDIFF(MONTH,@StartDate,@EndDate)

SELECT @MonthDiff - 
		CASE 
			WHEN FORMAT(@StartDate,'dd') > FORMAT(@EndDate,'dd') THEN 1
			ELSE 0
		END

You can create this function to calculate absolute difference between two dates. As I found using DATEDIFF inbuilt system function we will get the difference only in months, days and years. For example : Let say there are two dates 18-Jan-2018 and 15-Jan-2019. So the difference between those dates will be given by DATEDIFF in month as 12 months where as it is actually 11 Months 28 Days. So using the function given below, we can find absolute difference between two dates.

CREATE FUNCTION GetDurationInMonthAndDays(@First_Date DateTime,@Second_Date DateTime)

RETURNS VARCHAR(500)

AS

BEGIN

    DECLARE @RESULT VARCHAR(500)=''

    

    DECLARE @MONTHS TABLE(MONTH_ID INT,MONTH_NAME VARCHAR(100),MONTH_DAYS INT)

	INSERT INTO @MONTHS

	SELECT 1,'Jan',31

	union SELECT 2,'Feb',28

	union SELECT 3,'Mar',31

	union SELECT 4,'Apr',30

	union SELECT 5,'May',31

	union SELECT 6,'Jun',30

	union SELECT 7,'Jul',31

	union SELECT 8,'Aug',31

	union SELECT 9,'Sep',30

	union SELECT 10,'Oct',31

	union SELECT 11,'Nov',30

	union SELECT 12,'Jan',31



	IF(@Second_Date>@First_Date)

	BEGIN



			declare @month int=0

			declare @days int=0



			declare @first_year int

			declare @second_year int



			SELECT @first_year=Year(@First_Date)

			SELECT @second_year=Year(@Second_Date)+1



			declare @first_month int

			declare @second_month int



			SELECT @first_month=Month(@First_Date)

			SELECT @second_month=Month(@Second_Date)	



			if(@first_month=2)

			begin

				   IF((@first_year%100<>0) AND (@first_year%4=0) OR (@first_year%400=0))

					 BEGIN

					  SELECT @days=29-day(@First_Date) 

					 END

				   else

					 begin

					  SELECT @days=28-day(@First_Date) 

					 end

			end

			else

			begin

			  SELECT @days=(SELECT MONTH_DAYS FROM @MONTHS WHERE MONTH_ID=@first_month)-day(@First_Date) 

			end



			SELECT @first_month=@first_month+1



			WHILE @first_year<@second_year

			BEGIN

			   if(@first_month=13)

			   begin

				set @first_month=1

			   end

			   WHILE @first_month<13

			   BEGIN

				   if(@first_year=Year(@Second_Date))

				   begin

					if(@first_month=@second_month)

					begin		    

					 SELECT @days=@days+DAY(@Second_Date)

					 break;

					end

					else

					begin

					 SELECT @month=@month+1

					end

				   end

				   ELSE

				   BEGIN

					SELECT @month=@month+1

				   END	    

				SET @first_month=@first_month+1

			   END



			SET @first_year  = @first_year  + 1

			END

	   

			select @month=@month+(@days/30)

			select @days=@days%30



			if(@days>0)

			begin

			 SELECT @RESULT=CAST(@month AS VARCHAR)+' Month '+CAST(@days AS VARCHAR)+' Days '

			end

			else 

			begin

			 SELECT @RESULT=CAST(@month AS VARCHAR)+' Month '

			end

		END



		ELSE

		BEGIN

		   SELECT @RESULT='ERROR'

		END



 

    RETURN @RESULT 

END

SELECT dateadd(dd,number,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS gun FROM master..spt_values
WHERE type = 'p'
AND year(dateadd(dd,number,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))=year(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

CREATE FUNCTION ufFullMonthDif (@dStart DATE, @dEnd DATE)
RETURNS INT
AS
BEGIN
	DECLARE @dif INT,
			@dEnd2 DATE
	SET @dif = DATEDIFF(MONTH, @dStart, @dEnd)
	SET @dEnd2 = DATEADD (MONTH, @dif, @dStart)
	IF @dEnd2 > @dEnd
		SET @dif = @dif - 1
	RETURN @dif
END
GO

SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-01')
SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-29')
SELECT dbo.ufFullMonthDif ('2009-04-30', '2009-05-30')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-05-15')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-05-16')
SELECT dbo.ufFullMonthDif ('2009-04-16', '2009-06-16')
SELECT dbo.ufFullMonthDif ('2019-01-31', '2019-02-28')

Making Some changes to the Above function worked for me.

CREATE FUNCTION [dbo].[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) - iif(EOMONTH(@DateY) = @DateY, 0, 1)
					ELSE DATEDIFF(MONTH, @DateX, @DateY)
				END
				)

RETURN @Result

END

Declare @FromDate datetime, @ToDate datetime, 
		@TotalMonth int ='2021-10-01', @TotalDay='2021-12-31' int, 
        @Month int = 0

   WHILE @ToDate > DATEADD(MONTH,@Month,@FromDate)
    	BEGIN
    		SET @Month = @Month +1
    	END
    SET @TotalMonth = @Month -1
    SET @TotalDay = DATEDIFF(DAY, DATEADD(MONTH,@TotalMonth, @FromDate),@ToDate) +1
    IF(@TotalDay = DAY(EOMONTH(@ToDate)))
    	BEGIN
    		SET @TotalMonth = @TotalMonth +1 
    		SET @TotalDay =0	
    	END

Result @TotalMonth = 3, @TotalDay=0

if you are using PostGres only --

SELECT (DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date)) * 12 +
              (DATE_PART('month', '2012-01-01'::date) - DATE_PART('month', '2011-10-02'::date));

There are a lot of answers here that did not satisfy all the corner cases so I set about to fix them. This handles:

• 01/05/2021 - 02/04/2021 = 0 months
• 01/31/2021 - 02/28/2021 = 1 months
• 09/01/2021 - 10/31/2021 = 2 months

I think this generally handles all the cases needed.

declare @dateX date = '01/1/2022'
declare @datey date = '02/28/2022'
-- select datediff(month, @dateX, @datey) --Here for comparison
SELECT
CASE 
    WHEN DATEPART(DAY, @DateX) = 1 and DATEPART(DAY, @DateY) = DATEPART(DAY, eomonth(@DateY))
    THEN DATEDIFF(MONTH, @DateX, @DateY) + 1
    WHEN DATEPART(DAY, @DateX) > DATEPART(DAY, @DateY) and DATEPART(DAY, @DateY) != DATEPART(DAY, eomonth(@DateY))
    THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
    ELSE DATEDIFF(MONTH, @DateX, @DateY)
END

I got some ideas from the other answers, but none of them gave me exactly what I wanted.

The problem boils down to what I perceive a "month between" to be, which may be what others are also looking for also.

For example 25th February to 25th March would be one month to me, even though it is only 28 days. I would also consider 25th March to 25th April as one month at 31 days.

Also, I would consider 31st January to 2nd March as 1 month and 2 days even though it is 30 days between.

Also, fractions of a month are a bit meaningless as it depends on the length of a month and which month in the range do you choose to take a fraction of.

So, with that in mind, I came up with this function. It returns a decimal, the integer part is the number of months and the decimal part is the number of days, so a return value of 3.07 would mean 3 months and 7 days.

CREATE FUNCTION MonthsAndDaysBetween (@fromDt date, @toDt date)
RETURNS decimal(10,2)
AS
BEGIN
    DECLARE @d1 date, @d2 date, @numM int, @numD int, @trc varchar(10);

    IF(@fromDt < @toDt)
    BEGIN
        SET @d1 = @fromDt;
        SET @d2 = @toDt;
    END
    ELSE
    BEGIN
        SET @d1 = @toDt;
        SET @d2 = @fromDt;
    END

    IF DAY(@d1)>DAY(@d2)
        SET @numM = year(@d2)*12+month(@d2)-year(@d1)*12-month(@d1)-1;
    ELSE
        SET @numM = year(@d2)*12+month(@d2)-year(@d1)*12-month(@d1);

    IF YEAR(@d1) < YEAR(@d2) OR (YEAR(@d1) = YEAR(@d2) AND MONTH(@d1) < MONTH(@d2))
    BEGIN
        IF DAY(@d2) < DAY(@d1)
            SET @numD = DAY(@d2) + DAY(EOMONTH(DATEADD(month,-1,@d2))) - DAY(@d1);
        ELSE
            SET @numD = DAY(@d2)-DAY(@d1);
    END
    ELSE
        SET @numD = DAY(@d2)-DAY(@d1);

    RETURN @numM + @numD / 100.0;
END

Try:

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));

This works fine for me on SQL SERVER 2000.

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)