Calculate age of a person in SQL [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

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

It seems like something simple, but it d开发者_运维知识库oesn't seem to work for me. I want to calculate the age of someone base on two dates in SQL.

I did DATEDIFF(year,Birthdate,ReferenceDate) and it doesn't always give me the right age.

For example

DATEDIFF(year,'1981-07-05',2011-07-01') 

gives 30, while it should still be 29. Is there a way to do this ?

Thanks,

---

Try this...

SELECT CASE WHEN
 (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) > @dateend)
THEN DATEDIFF(year, @datestart  ,@dateend) -1
ELSE DATEDIFF(year, @datestart  ,@dateend)
END

It just compares the year difference and if it is greater then subtracts a year, else it returns the value.

---

Declare @Date1 datetime
Declare @Date2 datetime


Select @Date1 = '07/25/1984'
Select @Date2 = GetDate()

select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age

---

Try This

select
    (datediff(yy,'1981-07-08',getdate()))-(datepart(yy,(convert(datetime,convert(int,dateadd(yy,datediff(yy,'1981-07-08',getdate()),'1981-07-08'))-convert(int,dateadd(yy,-1,getdate())))))-1900)