Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

Script which gives Wrong Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
 DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

Script which gives Correct Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
   DATEDIFF(YEAR,@DOB,GETDATE())
     -
   (CASE
      WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
            >  GETDATE() THEN 1
      ELSE 0 END)
   'Age in Years'

Correct Result:

Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.