calculating age in years - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calculating age in years

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-14 : 22:13:31

Hi friends
i need to calculate age in years and doing following
select DATEDIFF(yy, mytable.BIRTHDATE, GETDATE()) from mytable

but one my colleagues suggested as following
select CAST(DATEDIFF(dd, mytable.BIRTHDATE, GETDATE()) / 365.25 AS int) from mytable

which one is correct as i see slightly different results
for e.g for following dates
05/08/1986 12:00:00 a.m.
30/12/1899 12:00:00 a.m.

my query returns
19
106
but the 2nd query returns
20
107
please advise here.
Thanks

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-14 : 22:22:26

For datediff(year, ...) it will consider diff of 1 year even the diff is 1 day
see this

select datediff(year, '2005-12-31', '2006-01-01') = 1

----------------------------------
'KH'
everything that has a beginning has an end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-14 : 23:26:14

First, you really have to define what yout definition of age in years is.

The usual definition is the number birthdays you have reached or passed. For example, someone born 2000-02-15 would be 5 years old on 2006-02-14, but 6 years old on 2000-02-15.

If that is what you are after, this code will do it. Notice that by this logic, someone born on Feb 29 is a year older on Feb 28 in non-leap years, but is a year older on Feb 29 in leap years.


select
	Age =
	case
	when BirthDayThisYear <= Today
	then datediff(yy,BirthYearStart,CurrYearStart)
	else datediff(yy,BirthYearStart,CurrYearStart)-1
	end,
	Birth = convert(varchar(10),Birth,121),
	Today = convert(varchar(10),Today,121)
from
(
select	BirthDayThisYear =
	dateadd(yy,datediff(yy,BirthYearStart,CurrYearStart),Birth), 
	*
from
(
select	BirthYearStart = dateadd(yy,datediff(yy,0,Birth),0),
	CurrYearStart  = dateadd(yy,datediff(yy,0,Today),0),
	*
from
(
-- Load some test date pairs
select	Birth = convert(datetime,'2000-02-15'),
	Today = convert(datetime,'2006-02-14')
union all
select	Birth = convert(datetime,'2000-02-15'),
	Today = convert(datetime,'2006-02-15')
union all
select	Birth = convert(datetime,'2000-02-15'),
	Today = convert(datetime,'2006-02-16')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2006-02-27')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2006-02-28')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2006-03-01')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2008-02-27')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2008-02-28')
union all
select	Birth = convert(datetime,'2000-02-29'),
	Today = convert(datetime,'2008-03-01')

) aaa
) aa
) a

Results:


Age         Birth      Today      
----------- ---------- ---------- 
5           2000-02-15 2006-02-14
6           2000-02-15 2006-02-15
6           2000-02-15 2006-02-16
5           2000-02-29 2006-02-27
6           2000-02-29 2006-02-28
6           2000-02-29 2006-03-01
7           2000-02-29 2008-02-27
7           2000-02-29 2008-02-28
8           2000-02-29 2008-03-01

(9 row(s) affected)

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-14 : 23:33:13

thanks guys.
i'll take Michael approach on this one.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-19 : 17:14:44

Thanks Michael.
Its works greatly and have a quick question.
is following script same as abv ur script ? or its wrong ? Thanks for ur advise
DECLARE @dob smalldatetime; SET @dob = '19730105'
DECLARE @today smalldatetime; SET @today = GETDATE()

SELECT DATEDIFF(yy, @dob, @today) -
CASE WHEN MONTH(@dob) > MONTH(@today) OR (MONTH(@dob) = MONTH(@today) AND DAY(@dob) > DAY(@today))
THEN 1 ELSE 0 END

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-19 : 19:44:00

quote:
Originally posted by rajani

Thanks Michael.
Its works greatly and have a quick question.
is following script same as abv ur script ? or its wrong ? Thanks for ur advise
DECLARE @dob smalldatetime; SET @dob = '19730105'
DECLARE @today smalldatetime; SET @today = GETDATE()

SELECT DATEDIFF(yy, @dob, @today) -
CASE WHEN MONTH(@dob) > MONTH(@today) OR (MONTH(@dob) = MONTH(@today) AND DAY(@dob) > DAY(@today))
THEN 1 ELSE 0 END

Cheers

No, your script will produce a different answer in the case of a birthday of Feb 29 in a leap year, and Feb 28 in a non leap year. You could have tested this yourself with the test data I posted with my script.

You have the same issue if you are doing difference in months. Your algorithim would have the same issue with someone born on say May 31, 2000. Most people would consider them 1 month old on June 30, 2000, but your algorithim would say they are 0 months old.

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-19 : 19:51:24

sorry to trouble you on this Michael .
actually i used my examples with both scripts and they gave same results. thats why i asked you here before using the script in our code.
Yes , u r right it wont produce correct results with the leap year which i did not try.
Thanks for your help. I'll stick to ur script.
Thanks again.

Cheers

peter@KLM
Starting Member

6 Posts

Posted - 2006-02-20 : 09:44:42

May be you can try this calculation:

select Birth = convert(datetime,'2000-02-29'),
Today = convert(datetime,'2008-02-29'),
Years = convert(int,(datediff(d,convert(datetime,'2000-02-29'),convert(datetime,'1800-01-01'))-
datediff(d,convert(datetime,'2008-02-29'),convert(datetime,'1800-01-01'))
) /365.25 ),
Months= floor((
((datediff(d,convert(datetime,'2000-02-29'),convert(datetime,'1800-01-01'))-
datediff(d,convert(datetime,'2008-02-29'),convert(datetime,'1800-01-01'))
) /3.6525 ) -
convert(int,(datediff(d,convert(datetime,'2000-02-29'),convert(datetime,'1800-01-01'))-
datediff(d,convert(datetime,'2008-02-29'),convert(datetime,'1800-01-01'))
) /365.25 )*100 -- extract the years *100
) *.12 ) -- the remainder as part of the month

-- one disadvantage: Date values have to be entered 4 times.
-- restriction: minimum date is january 1th 1800

/*--------------------------------------------------
Explanation:
Number of days is calculated to reference date (jan 01, 1800) for both days. Then this figure is divide by 365.25 to find the years, end 3.6525 to find the months (after extracting the number of years ofcours)
------------------------------------------------------*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources