SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Calculating age in years
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

rdwilliamsjr
Starting Member

USA
4 Posts

Posted - 12/19/2001 :  12:43:29  Show Profile  Visit rdwilliamsjr's Homepage  Reply with Quote
--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 - 08/07/2003 :  21:00:35  Show Profile  Reply with Quote
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
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 08/09/2003 :  12:54:41  Show Profile  Visit Stoad's Homepage  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 08/09/2003 :  13:48:22  Show Profile  Reply with Quote
Does Arnold have anything to say about this

I think so.

Sam
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 08/09/2003 :  15:44:59  Show Profile  Visit Stoad's Homepage  Reply with Quote
I like the numerological approach... :)

- Vit
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 08/10/2003 :  07:34:37  Show Profile  Reply with Quote
I'd use the method Rob gives in his last posting in this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3406
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 08/10/2003 :  09:47:16  Show Profile  Visit Stoad's Homepage  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 08/10/2003 :  12:11:02  Show Profile  Reply with Quote
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.
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 08/10/2003 :  14:24:23  Show Profile  Visit Stoad's Homepage  Reply with Quote
Oh, yes. Cool.

- Vit
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/03/2003 :  16:16:45  Show Profile  Reply with Quote
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!
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 09/05/2003 :  07:22:28  Show Profile  Visit Stoad's Homepage  Reply with Quote
Brett:

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

X002548
Not Just a Number

15586 Posts

Posted - 09/05/2003 :  22:57:02  Show Profile  Reply with Quote
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!
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 09/06/2003 :  09:46:56  Show Profile  Visit Stoad's Homepage  Reply with Quote
Hm... You mean you know what 'opa' means?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/09/2003 :  16:47:26  Show Profile  Reply with Quote
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!
Go to Top of Page

weeeezzll
Starting Member

1 Posts

Posted - 11/07/2008 :  18:26:45  Show Profile  Reply with Quote
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.

Edited by - weeeezzll on 11/10/2008 16:29:11
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/07/2008 :  18:38:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/07/2008 :  18:45:25  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/07/2008 :  18:55:27  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/07/2008 :  18:58:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/07/2008 :  19:01:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 11/07/2008 19:18:17
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/10/2008 :  17:06:47  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000