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


Best way to Calculate Human Age from a Birthdate


Best way to Calculate Human Age from a Birthdate

Author
Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
Actually I think that a negative age has some real value if you think of age not as only the age of a person. What you are calculating is the total years elapsed between two dates and if you were looking at something like project delivery dates a negative data might have some real worth. Maybe a company that works with long term deliverables like construction. It might be nice to see that something was delivered more than a year ahead of schedule. Your fine code would work for such a scenario if it included negatives.

We have now seen several different approaches to the same problem. Most of them handle leap years correctly too. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3258 Visits: 11771
Sean Lange (1/8/2013)
Actually I think that a negative age has some real value if you think of age not as only the age of a person. What you are calculating is the total years elapsed between two dates and if you were looking at something like project delivery dates a negative data might have some real worth. Maybe a company that works with long term deliverables like construction. It might be nice to see that something was delivered more than a year ahead of schedule. Your fine code would work for such a scenario if it included negatives.

We have now seen several different approaches to the same problem. Most of them handle leap years correctly too. :-D


Going by the dictionary definition of age, "The length of time during which a being or thing has existed.", I have to say that negative age does not make much sense to me.

I tested the solutions from the other posts using the test data I posted (leaving out the negative ages), and found every one had at least one difference with the solution I posted, especially with the handling of Feb 29 birthdays, or when the time of day for CURR_DATE was before the time of day for DOB when they were both the same day of the year ( Example: DOB = 2013-01-08 16:52:54.810 and CURR_DATE = 2023-01-08 16:52:54.710 ). I believe most calculations of Age ignore time of day, so my solution is coded to ignore it.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024

Going by the dictionary definition of age, "The length of time during which a being or thing has existed.", I have to say that negative age does not make much sense to me.


I agree that as age negative doesn't make any sense. I was trying to point out the calculation could be used in other scenarios where a negative would make sense. Given that the thread is to calculate human age it certainly doesn't make any sense in that context.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
AndrewSQLDBA (1/8/2013)
Hello Everyone
Happy New Year!!!

I am fooling around with some code, and was wondering if there is a really great function to calculate the birthdate of a person. One that will take into count things like leap year. I am not getting into such fine details such as if the person was born on the west coast or the east coast type of calculations. Just a very good birthdate age calculation function.

If I pass in the birthdate, what is the persons age today. Most that I have used or tried, come up a bit short when it comes to returning a perfect calculation

Thanks in Advance
Andrew SQLDBA


So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3258 Visits: 11771
Jeff Moden (1/9/2013)
AndrewSQLDBA (1/8/2013)
Hello Everyone
Happy New Year!!!

I am fooling around with some code, and was wondering if there is a really great function to calculate the birthdate of a person. One that will take into count things like leap year. I am not getting into such fine details such as if the person was born on the west coast or the east coast type of calculations. Just a very good birthdate age calculation function.

If I pass in the birthdate, what is the persons age today. Most that I have used or tried, come up a bit short when it comes to returning a perfect calculation

Thanks in Advance
Andrew SQLDBA


So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? ;-)



As usual, no one agrees on this, but different countries do at least have some standard.
http://en.wikipedia.org/wiki/February_29
"...in England and Wales or in Hong Kong, a person born on February 29, 1996, will have legally reached 18 years old on March 1, 2014. If he or she was born in the United States, Taiwan or New Zealand, he or she legally becomes 18 on February 28, 2014, a day earlier..."


I prefer the Feb 28 date for birthdays in non-leap years because that's the "standard" for the US. And, it's easier to code in TSQL.:-D
math martinez
math martinez
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 62
Simple way to compute your age accurately.

A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

In this query you exactly get the age as is it.
Example 1
DOB = 11/15/1987 and
datenow =11/15/2012 the result would be
AGE=25

Example 2
DOB = 11/16/1987 and
datenow =11/15/2012 the result would be
AGE=24

SO HERE ARE THE QUERY

DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'

SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1

END AS AGE

HOPE I CAN HELP!
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: 24215 Visits: 37978
How about these solutions?



declare @DOB date = '20080229',
@CurDate date = '20130228';

select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;

set @CurDate = '20130301';

select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;

set @CurDate = '20130227';

select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;




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)
mdsharif532
mdsharif532
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 139
How about this..............

DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '2008-02-29'
SET @STPR_START_DATE = '2013-02-28'


SELECT CASE WHEN YEAR(@STPR_START_DATE)%400 != 0 AND DAY(@STPR_START_DATE)= 28 THEN

DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + (DAY(@BIRTH_DATE) -1) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END

ELSE

DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END

END AS AGE
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2673
Michael Valentine Jones (1/9/2013)
Jeff Moden (1/9/2013)


So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? ;-)


As usual, no one agrees on this, but different countries do at least have some standard.
http://en.wikipedia.org/wiki/February_29
"...in England and Wales or in Hong Kong, a person born on February 29, 1996, will have legally reached 18 years old on March 1, 2014. If he or she was born in the United States, Taiwan or New Zealand, he or she legally becomes 18 on February 28, 2014, a day earlier..."

I prefer the Feb 28 date for birthdays in non-leap years because that's the "standard" for the US. And, it's easier to code in TSQL.:-D




I hate to say it, but there is not actually a "standard" the U.S. - it depends on the precise purpose you're doing the date calculations for, and who needs them. Check with the business users about this, every time, particularly in regulated industries for for regulated/legal purposes.
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: 24215 Visits: 37978
mdsharif532 (1/10/2013)
How about this..............

DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '2008-02-29'
SET @STPR_START_DATE = '2013-02-28'


SELECT CASE WHEN YEAR(@STPR_START_DATE)%400 != 0 AND DAY(@STPR_START_DATE)= 28 THEN

DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + (DAY(@BIRTH_DATE) -1) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END

ELSE

DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END

END AS AGE


A lot of extra work for what can be done easily with a couple of datetime functions and a case statement.

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)
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