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

Answer 1

Try this

SELECT CASE WHEN DATEDIFF(d,'2013-09-01', '2013-11-15')>30 THEN DATEDIFF(d,'2013-09-01', '2013-11-15')/30.0 ELSE 0 END AS 'MonthDifference'

OR

SELECT DATEDIFF(DAY, '2013-09-01', '2013-11-15') / 30.436875E

Answer 2

DateDiff compares the values of the column you specify to work out the difference, it doesn't compare both dates and give you an exact difference. You've told it to compare the Month values, so thats all it's looking it.

http://technet.microsoft.com/en-us/library/ms189794.aspx

The Technet article details the return value of the DateDiff Function - note that it's only int.

If you want the value as an exact figure (or nearabouts), you should datediff the dates on days, then divide by 30. For neatness, I've also rounded to a single decimal place.

select Round(Convert(decimal, DateDiff(dd,@EmployeeStartDate, @EmployeeEndDate)) / 30, 1)