# Thread: Calculate age?

1. Registered User
Join Date
Jan 2004
Posts
35

## Unanswered: Calculate age?

I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?

2. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
select CONVERT(INTEGER, getDATE() - DOB)/365 from table

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
that's only approximate, and will probably be wrong for some people on some days

here's an age calculation that's correct to the day:
Code:
```select year(getdate())
- year(DOB)
- case when month(getdate())
> month(DOB)
then 0
else
case when month(getdate())
< month(DOB)
then 1
else
case when day(getdate())
< day(DOB)
then 1
else 0
end
end
end   as age
from ...```

4. Registered User
Join Date
Jan 2003
Location
London, England
Posts
106
SELECT DATEDIFF(yy, DOB, GETDATE())

my 2 cents...

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Frettmaestro, what's your birthday?

unless it is within the first four weeks of january, it has not yet happened this year

so do your datediff calculation on your own birthday, and see what answer you get

is that your correct age?

6. Registered User
Join Date
Jan 2003
Location
London, England
Posts
106
Hmm, busted! So much for quick solutions, hehe...I would have to go with the same solution as r123456... ->

SELECT CONVERT(int, DATEDIFF(dd, '1976-03-06 13:00:000', GETDATE()))/365

But how is this only approximate...?

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
it is only approximate because it totally messes up around the last day of february and/or first day of march, and it gets worse the older the person is

8. Registered User
Join Date
Jan 2003
Location
London, England
Posts
106
This means that a 80-year old person would risk to wait 20 days before his age was updated from 79 to 80. If this is the case then I guess I at least could live with that...and I bet the 80-year old guy would be thrilled, hehehe Just kidding offcourse...accuracy is *very* important in all aspects of what you do.

9. SQL Consultant
Join Date
Apr 2002
Location