2

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

It seems like something simple, but it doesn'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,

Community
  • 1
  • 1
David Brunelle
  • 6,528
  • 11
  • 64
  • 104
  • 4
    Your datepart is `year`, so it only considers `1981` and `2011`. Have a look at the [documentation](http://msdn.microsoft.com/en-us/library/ms189794.aspx). Also, havev a look at this question http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days – Jacob Jul 07 '11 at 13:14
  • 1
    There is an article that explains it, it doesn't calculate the year between the dates, it calculates the number of years between the years. http://www.sqlteam.com/article/datediff-function-demystified – Chris Diver Jul 07 '11 at 13:19

3 Answers3

7

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.

tkerwood
  • 1,875
  • 16
  • 26
0
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
Yogesh Bhadauirya
  • 1,225
  • 10
  • 10
  • SELECT DATEDIFF(yy, BirthDate, GETDATE()) - CASE WHEN MONTH(BirthDate) > MONTH(GETDATE()) OR (MONTH(BirthDate) = MONTH(GETDATE()) AND DAY(BirthDate) > DAY(GETDATE())) THEN 1 ELSE 0 END AS Age; – Howard Rothenburg Apr 20 '16 at 15:20
0

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)
SMK
  • 2,098
  • 2
  • 13
  • 21