How can we calculate Age of employee?

  • 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 AGE

    FROM EMP_MST

  • 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 days

    ARUN SAS

  • Ok thanks for replay...

    Tell me a another way to fetch the exact age of Different Date...

  • Hi,

    you can try this

    select 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 days

    from EMP_MST where accountid=4

    Hitendra

  • Hi,

    you can try this

    select 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 days

    from EMP_MST where accountid=4

    Hitendra

  • Hi,

    you can try this

    select 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 days

    from EMP_MST where accountid=4

    Hitendra

  • t.hitendra (5/11/2009)


    Hi,

    you can try this

    select 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 days

    from EMP_MST where accountid=4

    Hitendra

    Hi,

    create the function like

    CREATE FUNCTION AGE_ASOF_NOW (@EMP_DOB DATETIME,@curr_dt datetime)

    returns varchar(1000)

    AS

    BEGIN

    declare @AGE_ASOF_NOW varchar(100)

    declare @DDIFF int

    select @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_NOW

    END

    and call this function in the select statement like

    select Dbo.AGE_ASOF_NOW (DATE_OF_BIRTH,getdate())

    ARUN SAS

  • 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

  • declare @birthday datetime

    declare @ToDate datetime

    DECLARE @smonth TINYINT, @sday TINYINT, @syear SMALLINT

    DECLARE @emonth TINYINT, @eday TINYINT, @eyear SMALLINT

    DECLARE @months TINYINT, @days TINYINT, @years SMALLINT

    DECLARE @tdate SMALLDATETIME

    set @birthday = '15 feb 1969'

    --set @retireage = 65

    set @birthday = @birthday--getdate()

    set @ToDate = getdate()--dateadd(year, +@retireage, @birthday)

    --print @ToDate

    SET @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 - @syear

    SET @months = 0

    SET @days = 0

    IF (@emonth >= @smonth)

    SET @months = @emonth - @smonth

    ELSE

    BEGIN

    SET @years = @years - 1

    SET @months = @emonth + 12 - @smonth

    END

    IF (@eday >= @sday)

    SET @days = @eday - @sday

    ELSE

    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)

    END

    print '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 '

  • +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_DATEdatetime,

    @END_DATEdatetime

    )

    returnsint

    as

    /*

    Original code came from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

    Original 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)

    */

    begin

    declare @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

    end

    select

    @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

    end

    from

    (

    selectAnniversaryThisYear =

    dateadd(yy,datediff(yy,StartDateYearStart,EndDateYearStart),@START_DATE),

    StartDateYearStart,

    EndDateYearStart

    from

    (

    selectStartDateYearStart =

    dateadd(yy,datediff(yy,0,@START_DATE),0),

    EndDateYearStart =

    dateadd(yy,datediff(yy,0,@END_DATE),0)

    ) aa

    ) a

    return @AGE_IN_YEARS

    end

  • 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=62729

    This function returns age in years.

    Age Function F_AGE_IN_YEARS:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

  • Here is one article on calculating age, Calculating Age[/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.

  • 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 1

    DOB = 11/15/1987 and

    datenow =11/15/2012 the result would be

    AGE=25

    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @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())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

  • 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 1

    DOB = 11/15/1987 and

    datenow =11/15/2012 the result would be

    AGE=25

    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @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())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

  • The following combinations give a negative result (that is fi 10 yr 3 months and -17 days):

    (@dob, @today)

    19270424, 19920707

    19150131, 20000606

    19230622, 20031215

    19170124, 20020322

    I cannot figure out why that is. Any ideas?

    Grz,

    Robert

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply