| Author |
 Topic  |
|
rdwilliamsjr
Starting Member
USA
4 Posts |
 Posted - 12/19/2001 : 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 - 08/07/2003 : 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 - 08/09/2003 : 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
USA
3459 Posts |
Posted - 08/09/2003 : 13:48:22
|
Does Arnold have anything to say about this 
I think so.
Sam |
|
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 08/09/2003 : 15:44:59
|
I like the numerological approach... :)
- Vit |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 08/10/2003 : 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
United Kingdom
1961 Posts |
Posted - 08/10/2003 : 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 - 08/10/2003 : 14:24:23
|
Oh, yes. Cool.
- Vit |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 09/05/2003 : 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 - 09/05/2003 : 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 - 09/06/2003 : 09:46:56
|
| Hm... You mean you know what 'opa' means? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 09/09/2003 : 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 Posts |
Posted - 11/07/2008 : 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. |
Edited by - weeeezzll on 11/10/2008 16:29:11 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/07/2008 : 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)
USA
6997 Posts |
Posted - 11/07/2008 : 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)
USA
6997 Posts |
Posted - 11/07/2008 : 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
Sweden
29138 Posts |
Posted - 11/07/2008 : 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
Sweden
29138 Posts |
Posted - 11/07/2008 : 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" |
Edited by - SwePeso on 11/07/2008 19:18:17 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/10/2008 : 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 |
|
|
|
Topic |
|
|
|
Calculating age in years
