## Best way to Calculate Human Age from a Birthdate

 Author Message AndrewSQLDBA SSChampion Group: General Forum Members Points: 12048 Visits: 3427 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 SQLDBA Sean Lange SSC Guru Group: General Forum Members Points: 149509 Visits: 18575 There are tons of articles and forum posts around here. This is about the best in my opinion.http://www.sqlservercentral.com/Forums/Topic796803-338-1.aspx _______________________________________________________________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 Modens 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) AndrewSQLDBA SSChampion Group: General Forum Members Points: 12048 Visits: 3427 Thank You SeanThat worked perfectly.Greatly appreciate itAndrew SQLDBA mdsharif532 SSC Veteran Group: General Forum Members Points: 229 Visits: 139 DATEDIFF(YEAR,P.BIRTH_DATE,STPRD.STPR_START_DATE) - CASE WHEN MONTH(P.BIRTH_DATE)*100 + DAY(P.BIRTH_DATE) > MONTH(STPRD.STPR_START_DATE)*100 + DAY(STPRD.STPR_START_DATE) THEN 1 ELSE 0 END AS AGE, Sean Lange SSC Guru Group: General Forum Members Points: 149509 Visits: 18575 mdsharif532 (1/8/2013)DATEDIFF(YEAR,P.BIRTH_DATE,STPRD.STPR_START_DATE) - CASE WHEN MONTH(P.BIRTH_DATE)*100 + DAY(P.BIRTH_DATE) > MONTH(STPRD.STPR_START_DATE)*100 + DAY(STPRD.STPR_START_DATE) THEN 1 ELSE 0 END AS AGE,You really should look at the link I posted. It is simple, accurate and easy to understand which the above is not. This looks like you took this from a query you have in your system somewhere. What is STPRD.STPR_START_DATE? _______________________________________________________________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 Modens 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) Lynn Pettis SSC Guru Group: General Forum Members Points: 225296 Visits: 40420 mdsharif532 SSC Veteran Group: General Forum Members Points: 229 Visits: 139 What is STPRD.STPR_START_DATE? Student Program Start Date mdsharif532 SSC Veteran Group: General Forum Members Points: 229 Visits: 139 DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIMESET @BIRTH_DATE = '1975-01-07'SET @STPR_START_DATE = '2013-01-06'SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE)Result: 38SELECT 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 AS AGEResult: 37 Sean Lange SSC Guru Group: General Forum Members Points: 149509 Visits: 18575 mdsharif532 (1/8/2013)DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIMESET @BIRTH_DATE = '1975-01-07'SET @STPR_START_DATE = '2013-01-06'SELECT DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE)Result: 38SELECT 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 AS AGEResult: 37OK now it makes sense. I just couldn't figure out what the dates were supposed to be. :-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 Modens 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 SSC-Dedicated Group: General Forum Members Points: 36128 Visits: 11933 The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years.The concept of negative age doesn't seem to have any real world meaning, so my method returns null if the date of birth is before current date.`select a.DOB, a.CURR_DATE, Age = datediff(yy,a.DOB,a.CURR_DATE) + case -- Age is null when DOB before current date when datediff(dd,a.DOB,a.CURR_DATE) < 0 then null -- Subtract 1 if current date before birthday in current year when datediff(dd,dateadd(yy,datediff(yy,a.DOB,a.CURR_DATE),a.DOB),a.CURR_DATE) < 0 then -1 else 0 endfrom ( -- Test Data select DOB =convert(datetime,'20040407'), CURR_DATE = convert(datetime,'20060203') union all select getdate(),dateadd(ms,100,getdate()) union all select getdate(),dateadd(ms,-100,getdate()) union all select getdate(),dateadd(dd,-1,getdate()) union all select getdate(),dateadd(yy,10,dateadd(ms,-100,getdate())) union all select getdate(),dateadd(yy,10,dateadd(ms,100,getdate())) union all select '20040407','20040407' union all select '20040407','20050406' union all select '20040407','20050407' union all select '20040229','20060227' union all select '20040229','20060228' union all select '20040229','20060301' union all select '20040229','20080228' union all select '20040229','20080229' union all select '20060205','20060205' union all select '17530101 00:00:00.000','99991231 23:59:59.997' union all select '19500913', getdate() ) aorder by a.DOB, a.CURR_DATE`Results:`DOB CURR_DATE Age----------------------- ----------------------- -----------1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 82461950-09-13 00:00:00.000 2013-01-08 16:52:54.810 622004-02-29 00:00:00.000 2006-02-27 00:00:00.000 12004-02-29 00:00:00.000 2006-02-28 00:00:00.000 22004-02-29 00:00:00.000 2006-03-01 00:00:00.000 22004-02-29 00:00:00.000 2008-02-28 00:00:00.000 32004-02-29 00:00:00.000 2008-02-29 00:00:00.000 42004-04-07 00:00:00.000 2004-04-07 00:00:00.000 02004-04-07 00:00:00.000 2005-04-06 00:00:00.000 02004-04-07 00:00:00.000 2005-04-07 00:00:00.000 12004-04-07 00:00:00.000 2006-02-03 00:00:00.000 12006-02-05 00:00:00.000 2006-02-05 00:00:00.000 02013-01-08 16:52:54.810 2013-01-07 16:52:54.810 NULL2013-01-08 16:52:54.810 2013-01-08 16:52:54.710 02013-01-08 16:52:54.810 2013-01-08 16:52:54.910 02013-01-08 16:52:54.810 2023-01-08 16:52:54.710 102013-01-08 16:52:54.810 2023-01-08 16:52:54.910 10`