Sql-server – Date difference in months with decimal


My query is as follows:

SELECT DATEDIFF(MONTH, convert(datetime,'01/01/2015',103),convert(datetime,'15/02/2015',103) )

I'm getting the result as 1 month only.

I need to get the result as 1.5 months. (means 1 month 15 days).

How can i get the output?

Best Solution

Try this, it will calculate the exact percentage of a month "used". It should work on all dates.

DECLARE @from date = '2015-01-01'
DECLARE @to   date = '2015-02-15'

  datediff(month, @from, @to) - 1 +
  1-1.0*(day(@from)-1)/ day(dateadd(m, datediff(m,-1, @from), -1))
  + 1.0*(day(@to)-1)/ day(dateadd(m, datediff(m,-1, @to), -1))



Note that

from date = '2015-03-02'
to   date = '2015-04-02'

Will give a result in 1.001075268817 a bit higher than 1 because a smaller percentage of march month has passed before "from" than the percentage of april before "to"