Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Best way to Calculate Human Age from a Birthdate Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, January 08, 2013 3:07 PM
 SSChampion Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:23 PM Points: 10,854, Visits: 10,012
 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. _______________________________________________________________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)
Post #1404459
 Posted Tuesday, January 08, 2013 3:34 PM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 9:18 AM Points: 3,022, Visits: 10,988
 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. 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.
Post #1404472
 Posted Wednesday, January 09, 2013 7:22 AM
 SSChampion Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:23 PM Points: 10,854, Visits: 10,012
 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)
Post #1404776
 Posted Wednesday, January 09, 2013 8:03 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 3:45 PM Points: 34,540, Visits: 28,709
 AndrewSQLDBA (1/8/2013)Hello EveryoneHappy 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 calculationThanks in AdvanceAndrew SQLDBASo, 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1404809
 Posted Wednesday, January 09, 2013 9:05 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 9:18 AM Points: 3,022, Visits: 10,988
 Jeff Moden (1/9/2013)AndrewSQLDBA (1/8/2013)Hello EveryoneHappy 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 calculationThanks in AdvanceAndrew SQLDBASo, 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.
Post #1404853
 Posted Wednesday, January 09, 2013 11:40 PM
 Grasshopper Group: General Forum Members Last Login: Friday, September 20, 2013 6:55 PM Points: 14, Visits: 51
 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 1DOB = 11/15/1987 and datenow =11/15/2012 the result would be AGE=25 Example 2DOB = 11/16/1987 and datenow =11/15/2012 the result would be AGE=24SO HERE ARE THE QUERYDECLARE @DOB SMALLDATETIMESELECT @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())-1END AS AGEHOPE I CAN HELP!
Post #1405204
 Posted Thursday, January 10, 2013 12:28 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 7:15 PM Points: 22,086, Visits: 28,996
 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;`
Post #1405233
 Posted Thursday, January 10, 2013 8:25 AM
 Grasshopper Group: General Forum Members Last Login: Thursday, April 11, 2013 2:09 PM Points: 14, Visits: 97
 How about this.............. DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIMESET @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 THENDATEDIFF(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 ELSEDATEDIFF(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
Post #1405491
 Posted Thursday, January 10, 2013 11:47 AM
 Right there with Babe Group: General Forum Members Last Login: Thursday, December 05, 2013 12:55 PM Points: 751, Visits: 2,158
 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.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.
Post #1405580
 Posted Thursday, January 10, 2013 7:04 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 7:15 PM Points: 22,086, Visits: 28,996
 mdsharif532 (1/10/2013)How about this.............. DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIMESET @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 THENDATEDIFF(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 ELSEDATEDIFF(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 AGEA lot of extra work for what can be done easily with a couple of datetime functions and a case statement.
Post #1405721

 Permissions