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 Sunday, May 10, 2009 10:52 PM
 SSC Veteran Group: General Forum Members Last Login: Saturday, December 3, 2016 4:43 AM Points: 289, Visits: 1,346
 Here I have used some queries but i unable to get a exact solution...Please try else Am I Correct?My quires:SELECT EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,GETDATE())/365 AS AGEFROM EMP_MST Many Thanks!S.saravanan“I am a slow walker, but I never walk backwards- Abraham Lincoln”
Post #713840
 Posted Monday, May 11, 2009 12:27 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 Hi,Your output gives only the days,The age may come in the format of years months and days like 34 years 10Months and 15 daysARUN SAS
Post #713858
 Posted Monday, May 11, 2009 12:32 AM
 SSC Veteran Group: General Forum Members Last Login: Saturday, December 3, 2016 4:43 AM Points: 289, Visits: 1,346
 Ok thanks for replay...Tell me a another way to fetch the exact age of Different Date... Many Thanks!S.saravanan“I am a slow walker, but I never walk backwards- Abraham Lincoln”
Post #713862
 Posted Monday, May 11, 2009 1:16 AM
 Old Hand Group: General Forum Members Last Login: Wednesday, October 17, 2012 10:33 AM Points: 390, Visits: 405
 Hi, you can try thisselect EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as daysfrom EMP_MST where accountid=4Hitendra
Post #713886
 Posted Monday, May 11, 2009 1:16 AM
 Old Hand Group: General Forum Members Last Login: Wednesday, October 17, 2012 10:33 AM Points: 390, Visits: 405
 Hi, you can try thisselect EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as daysfrom EMP_MST where accountid=4Hitendra
Post #713887
 Posted Monday, May 11, 2009 1:16 AM
 Old Hand Group: General Forum Members Last Login: Wednesday, October 17, 2012 10:33 AM Points: 390, Visits: 405
 Hi, you can try thisselect EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as daysfrom EMP_MST where accountid=4Hitendra
Post #713888
 Posted Monday, May 11, 2009 2:11 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 t.hitendra (5/11/2009)Hi, you can try thisselect EMP_CODE,DATE_OF_BIRTH ,DATEDIFF(DD,DATE_OF_BIRTH,getdate())/365 as years,(DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365)/30 as months,DATEDIFF(DD,DATE_OF_BIRTH,getdate())%365%30 as daysfrom EMP_MST where accountid=4HitendraHi,create the function likeCREATE FUNCTION AGE_ASOF_NOW (@EMP_DOB DATETIME,@curr_dt datetime)returns varchar(1000)AS BEGIN declare @AGE_ASOF_NOW varchar(100)declare @DDIFF intselect @DDIFF = datediff(DAY,@EMP_DOB,@curr_dt)select @AGE_ASOF_NOW = cast((@DDIFF/365)as varchar(4))+' Years '+ cast((@DDIFF%365)/30 as varchar(3))+' Months '+ cast((@DDIFF%365)%30 as varchar(3))+' Days ' return @AGE_ASOF_NOWENDand call this function in the select statement like select Dbo.AGE_ASOF_NOW (DATE_OF_BIRTH,getdate())ARUN SAS
Post #713908
 Posted Wednesday, May 13, 2009 8:13 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, June 6, 2016 1:51 PM Points: 1,400, Visits: 3,059
 Please be careful using ANY advice from an open forum such as this one without testing it first. The algorithms offered so far are severely lacking in accuracy. Although as schoolchildren, we learned that there are 365 days in a year, most of us would agree that because of the nature of the rules for leap years, we cannot use either 365 or 365.25 as an accurate divisor. And, of course, you don't believe that every month has 30 days. I do appreciate the opportunity your request and the interim responses gave me to work up what I now believe to be an accurate script for returning an age in years, months and days. As has been suggested, it would probably be best implemented as a UDF (user defined function). Oh. One more thing. Despite my confidence in this code, you must be sure of it before using it. Test it with as many weird combinations of @Today and @DOB as you can to prove out its accuracy.`Declare @DOB smallDatetime ,@Today smallDatetime ,@AgeYears int ,@AgePlusMonths int ,@AgePlusDays int ,@LastBD datetime ,@LastMonthBD datetime--======================--set @DoB = '20040228'set @Today = '20080229'--======================--select @AgeYears = year(@today)-year(@dob) - case when month(@today) < month(@dob) or (month(@today) = month(@dob) and day(@today) < day(@dob)) then 1 else 0 end,@LastBD = dateadd(year,@AgeYears,@Dob) ,@AgePlusMonths = datediff(month, @LastBD, @Today) - case when month(@today) <= month(@LastBD) and day(@today) < day(@LastBD) then 1 else 0 end - case when month(@dob) = 2 and day(@dob) = 29 then 1 else 0 end ,@LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD),@AgePlusDays = datediff(day, @LastMonthBD, @Today)Select @today as today ,@DoB DoB ,@LastBD as LastBD ,@LastMonthBD as LastMonthBD ,@AgeYears AgeYears ,@AgePlusMonths As AgePlusMonths ,@AgePlusDays as AgePlusDays `
Post #716606
 Posted Thursday, May 14, 2009 2:12 AM
 SSC Veteran Group: General Forum Members Last Login: Monday, November 14, 2016 8:56 AM Points: 246, Visits: 2,386
 declare @birthday datetimedeclare @ToDate datetime DECLARE @smonth TINYINT, @sday TINYINT, @syear SMALLINTDECLARE @emonth TINYINT, @eday TINYINT, @eyear SMALLINTDECLARE @months TINYINT, @days TINYINT, @years SMALLINTDECLARE @tdate SMALLDATETIMEset @birthday = '15 feb 1969'--set @retireage = 65set @birthday = @birthday--getdate()set @ToDate = getdate()--dateadd(year, +@retireage, @birthday)--print @ToDateSET @smonth = MONTH(@birthday)SET @sday = DAY(@birthday)SET @syear = YEAR(@birthday)SET @emonth = MONTH(@ToDate)SET @eday = DAY(@ToDate)SET @eyear = YEAR(@ToDate)SET @years = @eyear - @syearSET @months = 0SET @days = 0IF (@emonth >= @smonth) SET @months = @emonth - @smonthELSE BEGIN SET @years = @years - 1 SET @months = @emonth + 12 - @smonth ENDIF (@eday >= @sday) SET @days = @eday - @sdayELSE BEGIN IF (@months > 0) SET @months = @months - 1 ELSE BEGIN SET @years = @years - 1 SET @months = @months + 11 END SET @tdate = DATEADD(yy,@years,@birthday) SET @tdate = DATEADD(m,@months,@tdate) SET @days = DATEDIFF(d,@tdate,@ToDate) ENDprint 'Accurate Age: (' +convert(varchar(11), @ToDate, 113) + ') ' + convert(varchar(3), @years) + ' Years ' + convert(varchar(3), @months) + ' Months ' + convert(varchar(3), @days) + ' Days ' + substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 1, 2) + ' Hours ' + substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 4, 2) + ' Minutes ' + substring(CONVERT(VARCHAR(8),DATEADD(ss,datediff(second, @birthday, @todate),0),108), 7, 2) + ' Seconds '
Post #716708
 Posted Thursday, May 14, 2009 12:19 PM
 SSC Eights! Group: General Forum Members Last Login: Wednesday, October 26, 2016 2:18 PM Points: 917, Visits: 2,673
 +1 on testing with weird dates (both date of birth and as-of dates). You must also know your business rule for leap day babies in non-leap-years. A version of what I use (not cleaned up! Please excuse bad table names, bad capitalization, and so on) follows.Note there is a test query and small selection of interesting test cases that can be run as required in the comments at the beginning of the function.`create function dbo.ddfn_UT_AgeInYears( @START_DATE datetime, @END_DATE datetime)returns intas/*Original code came from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462Original name F_AGE_IN_YEARS.Updated 20080109 PH - comments updated.Updated 20080423 PH - comments updated.Function ddfn_UT_AgeInYears computes Age in years. Input parameters @START_DATE and @END_DATE are required. If either or both parameters are null, the function returns null. if @START_DATE midnight is greater than @END_DATE midnight, the function returns NULL. i.e. SELECT dbo.ddfn_UT_AgeInYears('1988-02-29','2007-02-28') Age is defined as the number of anniversary dates reached or passed from @START_DATE through @END_DATE. Age is calculated based on midnight (00:00:00.000) of parameters @START_DATE and @END_DATE. Time of day is not used in the calculation. NULL is returned when the @END_DATE is earlier than the @START_DATE. For example, someone born 2000-02-15 would be 5 years old on 2006-02-14, but 6 years old on 2006-02-15. Someone born on Feb 29 would be a year older on Feb 28 in non-leap years, but would be a year older on Feb 29 in leap years. Function is valid for entire range of datetime values from 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997. Quick test code: declare @strSql varchar(8000) set @strSql = '' SET @strSql = 'select ' + 'DOB AS BirthDate, ' + 'AsOfDt AS AsOfDate, ' + 'database.dbo.ddfn_UT_AgeInYears(DOB,AsOfDt) AS UT_AgeInYears' + ' FROM ' + ' (SELECT ''2004-02-29'' AS DOB, ''2004-02-28'' AS AsOfDt' + ' union all SELECT ''2004-02-29'' AS DOB, ''2004-02-29'' AS AsOfDt' + ' union all SELECT ''2004-02-29'' AS DOB, ''2004-03-01'' AS AsOfDt' + ' union all SELECT '''' AS DOB, '''' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2007-02-27'' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2007-02-28'' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2007-03-01'' AS AsOfDt' + ' union all SELECT '''' AS DOB, '''' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2008-02-28'' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2008-02-29'' AS AsOfDt' + ' union all SELECT ''1988-02-29'' AS DOB, ''2008-03-01'' AS AsOfDt' + ') HardCodeDateList' exec (@strSQL)*/begindeclare @AGE_IN_YEARS int-- Start out by setting the times to midnight.select @START_DATE = dateadd(dd,datediff(dd,0,@START_DATE),0), @END_DATE = dateadd(dd,datediff(dd,0,@END_DATE),0)if @START_DATE > @END_DATE begin return null endselect @AGE_IN_YEARS = datediff(yy,StartDateYearStart,EndDateYearStart) + -- Subtract 1 if anniversary date is after end date case when AnniversaryThisYear <= @END_DATE then 0 else -1 endfrom(select AnniversaryThisYear = dateadd(yy,datediff(yy,StartDateYearStart,EndDateYearStart),@START_DATE), StartDateYearStart, EndDateYearStartfrom(select StartDateYearStart = dateadd(yy,datediff(yy,0,@START_DATE),0), EndDateYearStart = dateadd(yy,datediff(yy,0,@END_DATE),0)) aa) areturn @AGE_IN_YEARSend`
Post #717262

 Permissions