SQLServerCentral / SQL Server 7,2000 / T-SQL / How can we calculate Age of employee? / Latest Posts

RE: How can we calculate Age of employee?

Sergiy — Thu, 14 Feb 2013 21:48:23 GMT

Should be really way simpler[code="sql"]SELECT dob, [day], DATEDIFF(yy, 0, [day] - dob) Years, DATEDIFF(MM, 0, [day] - dob)%12 Months, DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, 0, [day] - dob), 0), [day] - dob) days FROM (SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob = CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0) d[/code]or even more simple:[code="sql"]SELECT dob, [day], YEAR([day] - dob)-1900 Years, MONTH([day] - dob)-1 Months, DAY([day] - dob)-1 days FROM (SELECT [day] = CONVERT(datetime, '1992-07-07', 120), dob = CONVERT(datetime, '1927-04-24', 120) UNION ALL -- 65SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0) d[/code]([day] - dob) gives you the age in seconds (milliseconds if you wish), and then you simply figure out YEAR, MONTH and DAY of that value.

RE: How can we calculate Age of employee?

john.arnott — Sat, 26 Jan 2013 13:26:50 GMT

Looks to me as though whenever "today's" date has a month later in the year than the birthday and a day-of-month smaller than the day part of the birthday, the algorithm will end up with a negative number of days. The result is mathematically correct, but could be adjusted for readability. For instance, with DOB=19270424 and Today=19920707, we would expect an age of 65 years and this code gives us that. It then reports that the age is 17 days less than 3 months more than the 65 years. You may want to examine the steps it took and adjust them to show 2 months and 13 days.----Edit: Corrected typo from [i]"... more than the 5 years." [/i]to [i]"... more than the [b]65[/b] years."[/i]

RE: How can we calculate Age of employee?

r_slot — Sat, 26 Jan 2013 02:50:42 GMT

Sorry for the small misunderstanding but I am using john.arnotts code. The results I mentioned before follow from the following code:[code="sql"]USE [SomeDatabase]GO/****** Object: UserDefinedFunction [dbo].[Age2] Script Date: 26-1-2013 10:41:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: Robert-- Create date: 23-01-2013-- Description: Calculating Age-- this function is not always OK-- =============================================ALTER FUNCTION [dbo].[Age2]( -- Add the parameters for the function here @Birthday As datetime, @Day_of_Decease As datetime)RETURNS nvarchar(100)ASBEGIN -- Declare the variables hereDeclare @Birth Datetime ,@Death Datetime ,@Today Datetime ,@AgeYears int ,@AgePlusMonths int ,@AgePlusDays int ,@LastBD datetime ,@LastMonthBD datetime ,@Result nvarchar(100)--======================--SET @Birth = @BirthdaySET @Death = @Day_of_DeceaseSET @Today = Getdate()--======================---- first part: athlete is still aliveIF @Death Is NULL BEGIN SET @AgeYears = year(@today)-year(@Birth) - case when month(@today) < month(@Birth) or (month(@today) = month(@Birth) and day(@today) < day(@Birth)) then 1 else 0 end SET @LastBD = dateadd(year,@AgeYears,@Birth) SET @AgePlusMonths = datediff(month, @LastBD, @Today) - case when month(@today) <= month(@LastBD) and day(@today) < day(@LastBD) then 1 else 0 end - case when month(@Birth) = 2 and day(@Birth) = 29 then 1 else 0 end SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD) SET @AgePlusDays = datediff(day, @LastMonthBD, @Today) END-- second part: the athlete is deceasedELSE BEGIN SET @AgeYears = year(@Death)-year(@Birth) - case when month(@Death) < month(@Birth) or (month(@Death) = month(@Birth) and day(@Death) < day(@Birth)) then 1 else 0 end SET @LastBD = dateadd(year,@AgeYears,@Birth) SET @AgePlusMonths = datediff(month, @LastBD, @Death) - case when month(@Death) < month(@LastBD) or (month(@Death) = month(@LastBD) and day(@Death) < day(@LastBD)) then 1 else 0 end - case when month(@Birth) = 2 and day(@Birth) = 29 then 1 else 0 end SET @LastMonthBD = dateadd(month,@AgePlusMonths,@LastBD) SET @AgePlusDays = datediff(day, @LastMonthBD, @Death) END-- third part: Return the result of the function SET @Result = CAST(@AgeYears As nvarchar(20)) + 'yr ' + CAST(@AgePlusMonths As nvarchar(20)) + 'mnth '+ CAST(@AgePlusDays As nvarchar(20)) + 'days' RETURN @ResultEND[/code]I hope this gives a better insight.Grz,Robert

RE: How can we calculate Age of employee?

ChrisM@home — Fri, 25 Jan 2013 11:03:33 GMT

[quote][b]Lynn Pettis (1/24/2013)[/b][hr][quote][b]r_slot (1/24/2013)[/b][hr]I am using your code of course.[/quote]Good. Now, if you would please post the code you are are using, not just refer me to my old code, we can see what may be happening.[/quote]It looks fine to me. Here's your code Lynn, tweaked only to read table values - the "errant" ones posted earlier:[code="sql"]SELECT [day], dob, age = datediff(yy,dob,[day]) - case when [day] < dateadd(yy,datediff(yy,dob,[day]), dob) then 1 else 0 endFROM (SELECT [day] = '1992-07-07', dob = '1927-04-24' UNION ALL -- 65SELECT [day] = '2000-06-06', dob = '1915-01-31' UNION ALL -- 85 SELECT [day] = '2003-12-15', dob = '1923-06-22' UNION ALL -- 80 SELECT [day] = '2002-03-22', dob = '1917-01-24' UNION ALL -- 85 SELECT [day] = '2008-02-28', dob = '2007-03-01' -- 0) d[/code]Results[font="Courier New"]day________dob________age1992-07-07 1927-04-24 652000-06-06 1915-01-31 852003-12-15 1923-06-22 802002-03-22 1917-01-24 852008-02-28 2007-03-01 0[/font]

RE: How can we calculate Age of employee?

Lynn Pettis — Thu, 24 Jan 2013 16:08:02 GMT

[quote][b]r_slot (1/24/2013)[/b][hr]I am using your code of course.[/quote]Good. Now, if you would please post the code you are are using, not just refer me to my old code, we can see what may be happening.

RE: How can we calculate Age of employee?

SQLRNNR — Thu, 24 Jan 2013 15:34:49 GMT

[quote][b]r_slot (1/24/2013)[/b][hr]I am using your code of course.[/quote]Which version of his code?What are your errors?What is the data that you are using for testing the code?

RE: How can we calculate Age of employee?

ChrisM@home — Thu, 24 Jan 2013 15:20:32 GMT

[quote][b]r_slot (1/24/2013)[/b][hr]I am using your code of course.[/quote]I haven't posted it yet! :w00t:

RE: How can we calculate Age of employee?

r_slot — Thu, 24 Jan 2013 15:10:58 GMT

I am using your code of course.

RE: How can we calculate Age of employee?

Lynn Pettis — Thu, 24 Jan 2013 07:12:52 GMT

[quote][b]r_slot (1/24/2013)[/b][hr]SQL Server 2012[/quote]What I think you were asked is what formula you are using to compute age, not what version of SQL Server.

RE: How can we calculate Age of employee?

r_slot — Thu, 24 Jan 2013 06:42:38 GMT

SQL Server 2012

RE: How can we calculate Age of employee?

Sergiy — Wed, 23 Jan 2013 13:33:07 GMT

[quote][b]r_slot (1/23/2013)[/b][hr]The following combinations give a negative result [/quote]What are you using?

RE: How can we calculate Age of employee?

r_slot — Wed, 23 Jan 2013 08:33:59 GMT

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

RE: How can we calculate Age of employee?

math martinez — Fri, 16 Nov 2012 02:02:38 GMT

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! :-)

RE: How can we calculate Age of employee?

math martinez — Fri, 16 Nov 2012 02:01:44 GMT

RE: How can we calculate Age of employee?

Lynn Pettis — Thu, 14 May 2009 13:16:28 GMT

Here is one article on calculating age, [url=http://www.sqlservercentral.com/articles/T-SQL/63351/][b]Calculating Age[/b][/url]. 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.

RE: How can we calculate Age of employee?

Michael Valentine Jones — Thu, 14 May 2009 13:07:33 GMT

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:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729[/url]This function returns age in years.Age Function F_AGE_IN_YEARS:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462[/url]

RE: How can we calculate Age of employee?

Nadrek — Thu, 14 May 2009 12:19:31 GMT

+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.[code]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[/code]

RE: How can we calculate Age of employee?

Fishbarnriots — Thu, 14 May 2009 02:12:00 GMT

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 '

RE: How can we calculate Age of employee?

john.arnott — Wed, 13 May 2009 20:13:56 GMT

Please be careful using [b]ANY[/b] 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.[code]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 [/code]

RE: How can we calculate Age of employee?

arun.sas — Mon, 11 May 2009 02:11:26 GMT

[quote][b]t.hitendra (5/11/2009)[/b][hr]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[/quote]Hi,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

RE: How can we calculate Age of employee?

t.hitendra — Mon, 11 May 2009 01:16:21 GMT

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

RE: How can we calculate Age of employee?

t.hitendra — Mon, 11 May 2009 01:16:20 GMT

RE: How can we calculate Age of employee?

t.hitendra — Mon, 11 May 2009 01:16:17 GMT

RE: How can we calculate Age of employee?

Saravanan_tvr — Mon, 11 May 2009 00:32:44 GMT

Ok thanks for replay...Tell me a another way to fetch the exact age of Different Date...

RE: How can we calculate Age of employee?

arun.sas — Mon, 11 May 2009 00:27:35 GMT

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

How can we calculate Age of employee?

Saravanan_tvr — Sun, 10 May 2009 22:52:59 GMT

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