SQl Difference in date

Question

I need find difference in two date in years but years should be in decimal example i need to find difference between 16-jun-2010 to 30-Sep-2014

and it should be 4.30 yrs

i try following way select DATEDIFF(YY,'16-jun-2010','30-sep-2014') as YearsOfServices

it give me 4 only

Answer 1

The built in function datediff() does is compute the number of period boundaries crossed between to dates.

datediff(yy,'16 jun 2014','30 Sep 2015')

returns 1

You'll get more accurate result if you compute the difference between the two dates in days and divide them by the mean length of a calendar year in days over a 400 year span (365.2425):

datediff(day,{start-date},{end-date},) / 365.2425

For example,

select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425

return 14.29461248 — just round it to the desired precision.