Q:

SQL Age Computed Column Always +1 Incorrect

I have a computed column that works out a persons age. What i have is as follows:
DATEDIFF(year,[DOB],COALESCE([DOD], GETDATE())

So if person is alive it works out current age from DOB, if they are deceased it works it out based on DOD and DOB. 
However at present the values given are not correct, mostly they report +1.
Can anyone offer any advice on how to fix this?
Sample data:  in all three instances it is reported +1
Thanks

A:

datediff() probably doesn't do what you really want.  As is well documented, it counts the number of "period breaks" between two values.  So, 2013-12-31 and 2014-01-01 are one year apart, according to this function.
One method that might be close enough is to switch to months:
DATEDIFF(month, [DOB], COALESCE([DOD], GETDATE()) / 12

Or, perhaps days are good enough:
DATEDIFF(day, [DOB], COALESCE([DOD], GETDATE()) / 365.25

A more accurate solution would be to add the estimated years to the original date.  If later than the current date, then subtract 1:
datediff(year, dob, COALESCE([DOD], GETDATE())) +
    (case when dateadd(year, datediff(year, dob, COALESCE([DOD], GETDATE())), dob) > COALESCE([DOD], GETDATE())
          then -1 else 0
     end)

Here is a SQL Fiddle example.

