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 8, 2013 9:33 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, August 24, 2016 12:43 PM Points: 1,004, Visits: 3,427
 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
Post #1404342
 Posted Tuesday, January 8, 2013 9:42 AM
 SSCoach Group: General Forum Members Last Login: Today @ 9:15 AM Points: 16,138, Visits: 16,843
 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 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 #1404347
 Posted Tuesday, January 8, 2013 11:18 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, August 24, 2016 12:43 PM Points: 1,004, Visits: 3,427
 Thank You SeanThat worked perfectly.Greatly appreciate itAndrew SQLDBA
Post #1404394
 Posted Tuesday, January 8, 2013 11:27 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, May 11, 2016 12:46 PM Points: 19, 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,
Post #1404399
 Posted Tuesday, January 8, 2013 12:13 PM
 SSCoach Group: General Forum Members Last Login: Today @ 9:15 AM Points: 16,138, Visits: 16,843
 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 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 #1404421
 Posted Tuesday, January 8, 2013 12:16 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 8:35 AM Points: 23,516, Visits: 37,736
Post #1404422
 Posted Tuesday, January 8, 2013 1:16 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, May 11, 2016 12:46 PM Points: 19, Visits: 139
 What is STPRD.STPR_START_DATE? Student Program Start Date
Post #1404439
 Posted Tuesday, January 8, 2013 1:23 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, May 11, 2016 12:46 PM Points: 19, 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
Post #1404440
 Posted Tuesday, January 8, 2013 2:29 PM
 SSCoach Group: General Forum Members Last Login: Today @ 9:15 AM Points: 16,138, Visits: 16,843
 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. _______________________________________________________________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 #1404449
 Posted Tuesday, January 8, 2013 2:54 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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_DATEResults: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
Post #1404457

 Permissions