Please refer the below examples and kindly let me know your ideas. Output = 3 expected output = 2.5 Since I have only 15 days in Nov, So I should get 0.5 for Nov Answer Try this OR

Month difference between two dates in sql server

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

​x
 
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

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'

 
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'​

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

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

Advertisement

Answer