Month difference between two dates in sql server

Question

Please refer the below examples and kindly let me know your ideas.

declare @EmployeeStartDate datetime='01-Sep-2013'
declare @EmployeeEndDate datetime='15-Nov-2013'
select DateDiff(mm,@EmployeeStartDate, DateAdd(mm, 1,@EmployeeEndDate))

Output = 3 expected output = 2.5

Since I have only 15 days in Nov, So I should get 0.5 for Nov

user_355714 · Answer

As far as I can tell, none of the other answers take into account that not all months are exactly 30 days long.

This is what I came up with:

DECLARE @StartDate DATETIME = '07-Oct-2018'
DECLARE @EndDate DATETIME = '06-Nov-2018'
SELECT
    DATEDIFF(m, @StartDate, @EndDate)
    + 1.0 * DAY(@EndDate) / DAY(EOMONTH(@EndDate))
    - 1.0 * DAY(@StartDate) / DAY(EOMONTH(@StartDate))

-- 0.974193548388

The formula can be explained like this:

The difference in months as output by DATEDIFF(m, ..., ...)
Plus the day of the last date divided by the number of days in that month
Minus the day of the first date divided by the number of days in that month

Note that in this case the answer from Ren Yuzhi gives the result 1.006451612904. The 1.0 is necessary to make the division happen in floating point rather than integer.

user_1308314 · Answer

declare @EmployeeStartDate datetime='013-09-01'
declare @EmployeeEndDate datetime='2013-11-15'
SELECT DATEDIFF(month, @EmployeeStartDate, @EmployeeEndDate)

Thanks for this. https://www.w3schools.com/sql/func_sqlserver_datediff.asp

user_8069026 · Answer

You can use Below to calculate the No Of Months between two Dates in MySQL,

PERIOD_DIFF(concat(year(Date1),LPAD(month(Date1),2,0)),concat(year(Date2),LPAD(month(Date2),2,0)))

Month difference between two dates in sql server

7 Answers