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

 How can we calculate Age of employee? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 14, 2009 1:07 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 9:18 AM Points: 3,022, Visits: 10,988
 These are links to my original post of the function from the prior post on this thread, plus another that you might find useful.Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.This function returns age in format YYYY MM DD.Age Function F_AGE_YYYY_MM_DD:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729This function returns age in years.Age Function F_AGE_IN_YEARS:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
Post #717302
 Posted Thursday, May 14, 2009 1:16 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 8:46 AM Points: 22,084, Visits: 28,987
 Here is one article on calculating age, Calculating Age. Give it a read as well as the discussion that followed. There may also be several more articles on calculating age on SSC, you might want to do a search of the site to see what else may turn up.
Post #717313
 Posted Friday, November 16, 2012 2:01 AM
 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/14/1987 and datenow =11/15/2012 the result would be AGE=24 SO 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 #1385545
 Posted Friday, November 16, 2012 2:02 AM
 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/14/1987 and datenow =11/15/2012 the result would be AGE=24 SO 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 #1385546
 Posted Wednesday, January 23, 2013 8:33 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, September 25, 2013 12:28 AM Points: 73, Visits: 157
 The following combinations give a negative result (that is fi 10 yr 3 months and -17 days):(@dob, @today)19270424, 1992070719150131, 2000060619230622, 2003121519170124, 20020322I cannot figure out why that is. Any ideas?Grz,Robert
Post #1410623
 Posted Wednesday, January 23, 2013 1:33 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 5:23 AM Points: 4,570, Visits: 8,297
 r_slot (1/23/2013)The following combinations give a negative result What are you using?
Post #1410777
 Posted Thursday, January 24, 2013 6:42 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, September 25, 2013 12:28 AM Points: 73, Visits: 157
 SQL Server 2012
Post #1411110
 Posted Thursday, January 24, 2013 7:12 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 8:46 AM Points: 22,084, Visits: 28,987
 r_slot (1/24/2013)SQL Server 2012What I think you were asked is what formula you are using to compute age, not what version of SQL Server.
Post #1411122
 Posted Thursday, January 24, 2013 3:10 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, September 25, 2013 12:28 AM Points: 73, Visits: 157
 I am using your code of course.
Post #1411362
 Posted Thursday, January 24, 2013 3:20 PM
 SSC Eights! Group: General Forum Members Last Login: Today @ 6:07 AM Points: 958, Visits: 4,545
 r_slot (1/24/2013)I am using your code of course.I haven't posted it yet! Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1411367

 Permissions