0

I have created a table in SSMS, and it has the following fields:

  • NAME varchar(50)
  • BIRTHDATE date
  • AGE decimal(3,0)

I want to use a formula for the AGE field, in the Table Designer to calculate the person's age based on their BIRTHDATE and the current date.

For example, NAME = 'Joe Blow' and BIRTHDATE = '1/1/1970'. I would like the AGE to be able to calculate this and return the age of '46'.

James Z
  • 12,209
  • 10
  • 24
  • 44
RJohnson
  • 19
  • 3
  • 2
    Possible duplicate of [How to calculate age in T-SQL with years, months, and days](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) – Ken White Jul 27 '16 at 02:23

1 Answers1

0

This is a bit tricky. It is tempting to use:

select datediff(year, birthdate, getdate())

However, this calculates the number of year boundaries (Jan 1sts) between two dates. And that is not the age.

One way to get the actual age is to subtract the current day of the year from each value. This normalization should work:

select datediff(year,
                dateadd(day, 1 - datepart(dy, birthdate), birthdate),
                dateadd(day, 1 - datepart(dy, birthdate), getdate())
               )

Even this might have a subtle off-by-one error in leap years. So, you can use brute force:

select (datediff(year, birthdate, getdate()) - 
        (case when datepart(month, birthdate) * 100 + datepart(day, birthdate) >
                   datepart(month, getdate()) * 100 + datepart(day, birthdate)
              then 1 else 0
         end)
        )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Or you could just use a close approximation using `DateDiff(day, birthdate, getdate()) / 365.25`. – Ken White Jul 27 '16 at 02:27
  • @KenWhite . . . Perhaps, but people are generally pretty particular about their age in years. – Gordon Linoff Jul 27 '16 at 02:35
  • I've never been offended when my age was miscalculated (during that few day window when it can be). I suppose I would have been if it had interfered with something important like being able to vote or drive, but in general it's often a viable solution. I guess it would depend on how exactly the calculated age was being used. – Ken White Jul 27 '16 at 02:38