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

General SQL Server Forums

Script Library

Calculating age in years

Author

Topic

rdwilliamsjr
Starting Member

4 Posts

Posted - 2001-12-19 : 12:43:29

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 21:00:35

Hello,

I see a small problem with your code, it doesn't seem to work right if the current month/day is equal to the birthdate month/day.

For example:

DOB = 08/07/2001
Current Date = 08/07/2003

Your routine returns 1, when it should return 2.

Kevin

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-09 : 12:54:41

declare @d1 datetime, @d2 datetime

set @d1='19880229' set @d2='20030228'

select
datediff(yy, @d1, @d2) +
case when
cast(cast(@d1 as char(6)) + ' 1964' as datetime) <=
cast(cast(@d2 as char(6)) + ' 1964' as datetime)
then 0
else -1
end

- Vit

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-09 : 13:48:22

Does Arnold have anything to say about this

I think so.

Sam

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-09 : 15:44:59

I like the numerological approach... :)

- Vit

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-10 : 07:34:37

I'd use the method Rob gives in his last posting in this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3406

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-10 : 09:47:16

The last Rob's formula from the link:

DECLARE @DOB datetime, @later datetime
SELECT @DOB='02/29/1988', @later='02/28/1989'
Select DateDIFF(yy,@DOB,@later)-
CASE WHEN @later>=DateAdd(yy,DateDIFF(yy,@DOB,@later), @DOB)
THEN 0 ELSE 1 END AS Age

Result: Age=1

My formula result: Age=0 (only on March 1st Age=1)

???????????????

- Vit

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-10 : 12:11:02

Ah well, if you want the same semantics as your method, you can swap the calculation around:

DECLARE @DOB datetime, @later datetime
SELECT @DOB='02/29/1988', @later='02/28/1989'
Select DateDIFF(yy,@DOB,@later)-
CASE WHEN @DOB<=DateAdd(yy,DateDIFF(yy,@later,@DOB), @later)
THEN 0 ELSE 1 END AS Age

However, the little research I did led me to believe that more people born on 29th February celebrate on 28th in non-leap years than on 1st March.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-10 : 14:24:23

Oh, yes. Cool.

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-03 : 16:16:45

Or how about my bio...

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-05 : 07:22:28

Brett:

let's wait till the end of October. Then we'll check it. Opa.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 22:57:02

quote:
Originally posted by Stoad

Brett:

let's wait till the end of October. Then we'll check it. Opa.

No problem...

Opa?

Why should it matter?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-06 : 09:46:56

Hm... You mean you know what 'opa' means?

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 16:47:26

quote:
Originally posted by Stoad

Hm... You mean you know what 'opa' means?

Lots of Russians over here....

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

weeeezzll
Starting Member

1 Post

Posted - 2008-11-07 : 18:26:45

Here is a summary/round up of this post:
THIS SOLUTION IS NOT CORRECT. LEFT INTACT FOR PROSPERITY. :D SEE NEW POST FOR CORRECT SOLUTION.

rdwilliamsjr: This solution is simplest and is accurate, except that he mistakenly uses FLOOR instead of CEILING. Dividing by 365.25 would work perfect if every year had 365.25 days in it (techinically it does), but our calendar is structured so that that .25 remainder is lumped up into a single year every 4 years. So as long as you are working with years in that fall on whole leap year intervals you are okay. The SQL below will illustrate why this is a problem:

SELECT
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1901') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1902') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1903') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1904') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1905') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1906') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1907') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1908') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1909') / 365.25) AS nvarchar(2)),
CAST(FLOOR(DATEDIFF(day, '12-31-1900', '12-31-1910') / 365.25) AS nvarchar(2))

RESULTS:
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
0 1 2 4 4 5 6 8 8 9
(1 row(s) affected)

The reason these numbers are like this is illustrated by removing the FLOAT and looking at the decimal value like so:

SELECT
CAST(DATEDIFF(day, '12-31-1900', '12-31-1901') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1902') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1903') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1904') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1905') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1906') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1907') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1908') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1909') / 365.25 AS nvarchar(9)),
CAST(DATEDIFF(day, '12-31-1900', '12-31-1910') / 365.25 AS nvarchar(9))

RESULTS:
--------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
0.999315 1.998631 2.997946 4.000000 4.999315 5.998631 6.997946 8.000000 8.999315 9.998631

(1 row(s) affected)

You can see that each year our calendar year falls shorts of an astrological year by .25 days. Then in the fourth year we catch up. Since FLOAT is returning the largest integer that is not bigger than the number it effectively rounds the values down.

By using CEILING we instead we return the lowest number that is not smaller than the number in question and effectively round up like so:

SELECT
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1901') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1902') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1903') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1904') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1905') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1906') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1907') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1908') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1909') / 365.25) AS nvarchar(2)),
CAST(CEILING(DATEDIFF(day, '12-31-1900', '12-31-1910') / 365.25) AS nvarchar(2))

RESULTS:
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 2 3 4 5 6 7 8 9 10

(1 row(s) affected)

CONCLUSION:
Below is the simplest accurate calculation using SQL:

CEILING(DATEDIFF(day, @Birthdate, @AgeAsOfDate) / 365.25)

There are other working solutions in this thread, but the others add unnecessary complexity.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 18:38:54

Nah...

SELECT	CASE
		WHEN DATEPART(day, @Birthdate) > DATEPART(day, @AgeAsOfDate) THEN DATEDIFF(month, @Birthdate, @AgeAsOfDate) - 1
		ELSE DATEDIFF(month, @Birthdate, @AgeAsOfDate)
	END / 12

See http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-07 : 18:45:25

quote:
Originally posted by weeeezzll
...
CONCLUSION:
Below is the simplest accurate calculation using SQL:

CEILING(DATEDIFF(day, @Birthdate, @AgeAsOfDate) / 365.25)

There are other working solutions in this thread, but the others add unnecessary complexity.

Except that it doesn't produce accurate results. Your code returns an age of one for someone only 10 months old.


select
	Age = ceiling(datediff(day,Birthdate,AgeAsOfDate)/365.25)
from
	( --Test Date
	select
		Birthdate   = convert(datetime,'20040228'), 
		AgeAsOfDate = convert(datetime,'20041231')
	) a 


Results:

Age                  
-------------------- 
1

(1 row(s) affected)

Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.
This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

For more info, see the link below.
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-07 : 18:55:27

quote:
Originally posted by Peso

Nah...
SELECT CASE
WHEN DATEPART(day, @Birthdate) > DATEPART(day, @AgeAsOfDate) THEN DATEDIFF(month, @Birthdate, @AgeAsOfDate) - 1
ELSE DATEDIFF(month, @Birthdate, @AgeAsOfDate)
END / 12
See http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.63"
N 56°04'39.26"

Our approaches differ for those people born on Feb 29.

I believe most consider themselves to be a year older on Feb 28 in non-leap years.


select
	Age = 
	CASE
	WHEN DATEPART(day, Birthdate) > DATEPART(day, AgeAsOfDate)
	THEN DATEDIFF(month, Birthdate, AgeAsOfDate) - 1
	ELSE DATEDIFF(month, Birthdate, AgeAsOfDate)
	END / 12,
	Age2 = dbo.F_AGE_IN_YEARS(Birthdate,AgeAsOfDate)
from
	( --Test Date
	select
		Birthdate   = convert(datetime,'20000229'), 
		AgeAsOfDate = convert(datetime,'20070228')
	) a 

Results:
Age         Age2        
----------- ----------- 
6           7

(1 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 18:58:34

weeeezzll, you should check your results first before posting.

See these dates


birthdate	ageasofdate	weeeezzll	Peso
2000-01-01	2000-03-04	1		0
1900-01-01	7375-02-10	5475		5475
1900-01-01	7375-02-11	5476		5475

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-07 : 19:01:56

quote:
Originally posted by Michael Valentine Jones

Our approaches differ for those people born on Feb 29.

They may be one year older on Feb 28 instead of Mar 1.
But here we must separate legal age and subjective age.

The only difference between our approaches is when a person is born on Feb 29 and you want to calculate the age on Feb 28 in non-leap-year years.

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-10 : 17:06:47

quote:
Originally posted by Peso

quote:
Originally posted by Michael Valentine Jones

Our approaches differ for those people born on Feb 29.

They may be one year older on Feb 28 instead of Mar 1.
But here we must separate legal age and subjective age.

The only difference between our approaches is when a person is born on Feb 29 and you want to calculate the age on Feb 28 in non-leap-year years.

E 12°55'05.63"
N 56°04'39.26"

Opinions vary on the proper day for someone born on Feb 29 to observe their birthday in non-leap year, and may actually change from one law to another in the same jurisdiction.

SQL Server seems to favor Feb 28:

select [One Year Birthday] = dateadd(year,1,'20040229')

Results:

One Year Birthday                                      
------------------------------------------------------ 
2005-02-28 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources