Calculating Age
-
samsonjr2 (3/14/2009)
I like to use computative column with datediff to calculate the age.Yes, its a shame you can't make it 'Persited' though, this means SQL has to recalc it with every query that includes that column.
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Yes you are correct it recalcs.
-
rmattaway (3/13/2009)
One method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.Most of the day-based solutions suffer from a fatal flaw:
My birthdays are never 365.25 (or .242199, or .2425) days apart.
They are either 365 or 366 days apart, with the number of days depending on:
1. Leap Years (this year or last year)
2. When I was born in the year: Before Feb 29th, on Feb 29th or after Feb 29th
3. The rules for Leapling birthdays in non-Leap Years
And for most real-world applications, being exact is important.
I want to be able to drink, drive, receive benefits, and get paid more on my birthday.
Approximations just aren't good enough 🙂
-
Tim,
Exactly!
Suppose the case of my friend, who is born on May 11th, 1993. On their 18th birthday, the system using 365.25 will declare them still 17 - which can get them into all kinds of trouble depending on how they choose to celebrate legal drinking age (in Australia).
On the other hand, someone born on Feb 29th, 1992 will still be considered legally 17 on Feb 28th, 2010, and shouldn't be able to go drinking that day (or worse, be tried as an adult for some crime).
This is why using the YYYYMMDD method works best.
20110511 - 19930511 = 180000
20100228 - 19920229 = 179999
datediff(day,'19930511','20110511') / 365.25 = 17.998631
And Lynn's method declares the leap-year person to be 18 on the 28th.
Anyway - I think we all agree that 'age' can be awkward if the method for calculating isn't great.
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
'works out the age of the apprentice.
Dim total As String
Dim strDOB As String = Format(CDate(.DateOfBirth), "MM dd")
Dim todaysdate As String = Format(CDate(Date.Today), "MM dd")
If strDOB > todaysdate Then
strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Hasn't had birthday yet this year
todaysdate = Format(CDate(Date.Today), "yyyy")
total = todaysdate - strDOB - 1
Else
strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Has had birthday this year
todaysdate = Format(CDate(Date.Today), "yyyy")
total = todaysdate - strDOB
End If
p1_txtAge.Value = total
p1_txtAge.Flatten = True
this is something i wrote in VB.NET
don't know if its any more accurate when working out the leap year issue
-
And Lynn's method declares the leap-year person to be 18 on the 28th.
Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
ScottPletcher (3/16/2009)
And Lynn's method declares the leap-year person to be 18 on the 28th.
Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. 🙂
I also put a disclaimer in my article regarding leaplings. For purposes of my article I made a "business" decision to keep their birthday in February. Based on legal requirements, this could change, and we would then have to account for that in our calculations.
-
For calculating age in the form of x Years y Months z Days check the post of mine
http://aspxdev.blogspot.com/2008/09/get-date-diference-in-form-of-x-years-y.html
-
This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same:
declare @BirthDate DATETIME
set @BirthDate = '08/27/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
print 'TODAY IS THE TWENTY-SIXTH'
set @BirthDate = '08/26/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
set @BirthDate = '08/25/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
set @BirthDate = '08/24/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
The following translates the dates into YYYYMMDD and gets the correct age, changing at at the month and year of the birtdate.
DECLARE
@CURRENT_YEAR INTEGER
, @CURRENT_MONTHS INTEGER
, @CURRENT_DAYS INTEGER
, @CURRENT_YYYYMMDD INTEGER
, @AGE_INTEGER INTEGER
SET @CURRENT_YEAR = DATEPART(YEAR, GETDATE())
SET @CURRENT_MONTHS = DATEPART(MONTH, GETDATE())
SET @CURRENT_DAYS = DATEPART(DAY, GETDATE())
SET @CURRENT_YYYYMMDD = (@CURRENT_YEAR * 10000) + (@CURRENT_MONTHS * 100) + @CURRENT_DAYS
SELECT 'CURRENT_DATE = ' , @CURRENT_YYYYMMDD
declare @BirthDate DATETIME
, @YEARS INTEGER
, @MONTHS INTEGER
, @DAYS INTEGER
, @BIRTH_YYYYMMDD INTEGER
set @BirthDate = '08/27/1980'
set @YEARS = DATEPART(YEAR, @BIRTHDATE)
SET @MONTHS = DATEPART(MM, @BIRTHDATE)
SET @DAYS = DATEPART (DD, @BIRTHDATE)
SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS
SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000
SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD
SELECT '@AGE_INTEGER = ' , @AGE_INTEGER
PRINT 'TODAY IS THE TWENTY-SIXTH'
set @BirthDate = '08/26/1980'
set @YEARS = DATEPART(YEAR, @BIRTHDATE)
SET @MONTHS = DATEPART(MM, @BIRTHDATE)
SET @DAYS = DATEPART (DD, @BIRTHDATE)
SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS
SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000
SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD
SELECT '@AGE_INTEGER = ' , @AGE_INTEGER
set @BirthDate = '08/25/1980'
set @YEARS = DATEPART(YEAR, @BIRTHDATE)
SET @MONTHS = DATEPART(MM, @BIRTHDATE)
SET @DAYS = DATEPART (DD, @BIRTHDATE)
SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS
SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000
SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD
SELECT '@AGE_INTEGER = ' , @AGE_INTEGER
set @BirthDate = '08/24/1980'
set @YEARS = DATEPART(YEAR, @BIRTHDATE)
SET @MONTHS = DATEPART(MM, @BIRTHDATE)
SET @DAYS = DATEPART (DD, @BIRTHDATE)
SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS
SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000
SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD
SELECT '@AGE_INTEGER = ' , @AGE_INTEGER
-
David. (8/27/2009)
This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same:declare @BirthDate DATETIME
set @BirthDate = '08/27/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
print 'TODAY IS THE TWENTY-SIXTH'
set @BirthDate = '08/26/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
set @BirthDate = '08/25/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
set @BirthDate = '08/24/1980'
print @BirthDate
print getdate()
SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE())
Your right, DATEDIFF(yy, @birthdate, getdate()) will return the same value regardless if the @birthdate is before on or after GETDATE(). That is the nature of the DATEDIFF function. TO actually calculate age requires additional calculations to make the final determination.
Consider this, DATEDIFF(yy,'2008-12-31','2009-01-01') returns 1 as there is a difference in year periods is 1 even though there is actually only a difference of 1 day.
-
Here is another way I calculate age.
SELECT @AGE = ' '+CAST(((datediff(ss, @DOB,GETDATE())) /31536000) AS NVARCHAR(MAX)) +
' Years, ' +
CAST((((datediff(ss, @DOB,GETDATE())) %31536000) /2628000) AS NVARCHAR(MAX)) +
' Months, ' +
CAST((((datediff(ss, @DOB,GETDATE())) %2628000) /86400) AS NVARCHAR(MAX)) +
' Days, ' +
CAST((((datediff(ss, @DOB,GETDATE())) %86400) /3600) AS NVARCHAR(MAX)) +
' Hours, ' +
CAST((((datediff(ss, @DOB,GETDATE())) %3600) /60) AS NVARCHAR(MAX)) +
' Minutes, ' +
CAST(((datediff(ss, @DOB,GETDATE())) %60) AS NVARCHAR(MAX)) +
' Seconds '
-
/*
Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)
average days in a year: 365.2425
average days ion a month: 30.436875
USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')
RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS
*/
CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDatedatetime = NULL, @EndDatedatetime = NULL)
RETURNS varchar(7)
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN (
RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+
RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +
RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)
)
END;
-
onecaring (10/12/2011)
/*Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)
average days in a year: 365.2425
average days ion a month: 30.436875
USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')
RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS
*/
CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDatedatetime = NULL, @EndDatedatetime = NULL)
RETURNS varchar(7)
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN (
RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+
RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +
RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)
)
END;
Interesting, if I set @StartDate = '1/1/2009' and @EndDate = '1/1/2010', I get 0001130, not one year. setting @EndDate = '1/2/2010' I get 0010001.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?
One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.
It is only my opinion... no one has to agree but positive criticism is most welcome 😀
-
onecaring (10/13/2011)
Interesting find. When does someone turn EXACTLY 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.
It is only my opinion... no one has to agree but positive criticism is most welcome 😀
With the precision of years/months/days, I'd say they're exactly one year old on their birthday.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 76 through 90 (of 95 total)
You must be logged in to reply to this topic. Login to reply