Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Age


Calculating Age

Author
Message
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3950 Visits: 6686
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. Smile

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24253 Visits: 37978
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. Smile


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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
aalokitoaami
aalokitoaami
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 19
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

http://aspxdev.blogspot.com
Remove from list
Remove from list
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 35
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24253 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
rbartram-847800
rbartram-847800
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 107
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 '
onecaring
onecaring
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 62
/*
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] (@StartDate datetime = NULL, @EndDate datetime = 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;
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 19324
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] (@StartDate datetime = NULL, @EndDate datetime = 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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
onecaring
onecaring
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 62
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 :-D
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1426 Visits: 19324
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 :-D
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search