calculating age from sysdate and birthdate using SQL Server [duplicate]

Question

Possible Duplicate:
How to calculate age in T-SQL with years, months, and days

I'm simply trying to calc age (in years) using a birthdate variable

1932-04-29 00:00:00.000

and SYSDATETIME( ) in SQL Server, where by

SELECT year(Sysdatetime() - Birthdate) as Age

produces (surprisingly) : 1980

What did I miss? I expected to get 80!

Answer 1

Calculating age is not as simple as it might first appear.

If you use Datediff you get a difference in absolute years, which is not the age.

eg

select DATEDIFF(yy, '1980-12-31', getdate())

will return 32, whereas the age of the person in question is 31.

This might be accurate enough for your purposes. More accurate, but still wrong, you can use

select convert(int,DATEDIFF(d, '1933-10-31', getdate())/365.25)

which is right most of the time.

Or you can write a more complex function....