title: A fractional DATEDIFF for SQLServer?
body: <p>Found out something recently (and sure, my fault for not reading the docs) that reasonably horrified me about SQLServer's DATEDIFF function; it counts the number of interval boundaries between the two specified dates.</p>
  
  <p>This means I can ask it for the number of days between <code>01 Jan 23:57</code> and <code>01 Jan 23:59</code> and it will return 0, but if I ask for the days between <code>01 Jan 23:59</code> and <code>02 Jan 00:01</code> it will tell me there is 1 day between them. The timespan is the same: 2 minutes, but suddenly one is a difference of 0 days and one is a difference of 1 day</p>
  
  <p>Coming from an Oracle and .Net background I can see I've made a gross error in assuming that DATEDIFF in TSQL worked equivalently (i.e. prepare a timespan and then round it to the specified interval), but what is the alternative? </p>
  
  <p>If I want to find out exactly, with decimal places, how many years there are between 2 dates, how do I do it in SQLServer? I don't want a result from 01 Jan to 31 Dec returning 0 years, but 31 dec 2000 to 01 jan 2002 returning 2 years, because these are gross errors and miles away from the 0.997 and 1.005 (not exact calcs) they should more likely be..</p>
  
  <p>The answer clearly isn't to DATEDIFF the days and divide by 365.0, not only because datediff is routinely "wrong" even for DAYS (as per my 2 minute example) but also because there aren't always 365 days in a year. Same for months -> they aren't always a specified number of intervals long, so it doesn't make sense to take days and divide by 31 (or 30, 29, or 28).. For the same reasons I cannot do simple <code>(endDateTime - startDateTime)/x</code> math</p>