user1769881 user1769881 - 4 months ago 33
SQL Question

Best way to DateDIFF Year based upon Month and DAY in SQL Microsoft Server 2008

I am trying to figure out what is the best and accurate possible way to calculate the difference between Years using MONTH and DAY. My attempt to this solution is: -

[CODE]CEILING(DATEDIFF(YEAR,DATE1,DATE2))[/CODE]

I want it to count 1.2 years, and round it up to 2 in final result, but it doesn't do that.

Answer

Perhaps doing the difference in months will be sufficient:

CEILING(DATEDIFF(MONTH, DATE1, DATE2) / 12.0) as Total

Note: this requires about a 13 month difference to be considered two years.