How old is a person 1

[ashab02]

Hello

I have a date of birth field but I would like to work out how old a person is based on todays date

Can somebody advise?

Shab

 

[vongrunt]

Years only?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ashab02]

yes years will be fine.

 

[AtomicWedgie]

select datediff(yyyy,[date of birth], getdate())

 

[ashab02]

Thanks, thats worked great.

 

[vongrunt]

> select datediff(yyyy,[date of birth], getdate())

DATEDIFF(yyyy) does NOT count elapsed years. It counts how many interval boundaries (New Year evenings in this case) elapsed.

So if today is 01/01 and person was born yesterday (12/31) that query will report 1 year. Since when 1 day equals 1 year?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[TysonLPrice]

This link is good discussion on it with some "solutions".

http://p2p.wrox.com/topic.asp?TOPIC_ID=6834

 

[vongrunt]

Let's make it simple...

Today is 04/06/2006.
Suppose person's birthdate is 04/17/1976.

Convert both dates to unseparated ISO strings and then integers. You get 20060406 and 19760417 respectively.

Subtract these two values. Result is 299989.

Remove last four digits. Result is 29. Voila.

For example:

SELECT
	LastName, FirstName,
	[!](convert(int, convert(varchar(8), getdate(), 112)) - convert(int, convert(varchar(8), BirthDate, 112)))/10000 as Age[/!]
FROM Persons

This is slightly more complex but absolutely accurate calendar-wise.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ashab02]

Thanks everyone that is very usefull