Calculate age - Most easiest way

  • Comments posted to this topic are about the item Calculate age - Most easiest way

  • If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a select statement. It calculates age by converting the dates to yyyymmdd format, subracting one from the other, diving by 10000 and rounding down.

    FLOOR((CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))

    - CONVERT(INT,CONVERT(CHAR(8),[dob],112)))/10000)

  • Neat, although isn't the Floor function obsolete, since you're performing an Integer Division?

  • I've always liked this to get the age in years

    select cast(datediff(d,@dob,getdate())/365.25 as int)

  • andrew_dale (12/2/2014)


    If you only need age in years, then the following is easier as it does not need you to create a function so can be used directly in a select statement. It calculates age by converting the dates to yyyymmdd format, subracting one from the other, diving by 10000 and rounding down.

    FLOOR((CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))

    - CONVERT(INT,CONVERT(CHAR(8),[dob],112)))/10000)

    A person born on 2012-02-29 should be how old on 2014-02-28?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    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

  • dave.ott 20779 (12/2/2014)


    I've always liked this to get the age in years

    select cast(datediff(d,@dob,getdate())/365.25 as int)

    Try any date pair (DOB and reference date) where the month and the day are the same, such as

    2012-03-02 and2014-03-02.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    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

  • A person born on 2012-02-29 should be how old on 2014-02-28?

    1 year old still, the day has not rolled over to 1st March

    select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120228'))/10000)

    union all

    select '20120229' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120229'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120229'))/10000)

    union all

    select '20120301' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120301'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120301'))/10000)

    2012022822

    2012022912

    2012030112

  • Could the if statement be simplified to be...

    if datediff(dd,dateadd(yy,@age,@dob),@getdate) < 0

    set @age = @age - 1

  • andrew_dale (12/2/2014)


    A person born on 2012-02-29 should be how old on 2014-02-28?

    1 year old still, the day has not rolled over to 1st March

    select '20120228' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120228'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120228'))/10000)

    union all

    select '20120229' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120229'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120229'))/10000)

    union all

    select '20120301' , FLOOR((CONVERT(INT,'20140228') - CONVERT(INT,'20120301'))/10000) , FLOOR((CONVERT(INT,'20140301') - CONVERT(INT,'20120301'))/10000)

    2012022822

    2012022912

    2012030112

    Most cultures celebrate the birthday of a "Leaper" on the 28th February, so a Leaper born on 2012-02-29 would celebrate their first birthday on 2013-02-28 and their second on 2014-02-28.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    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

  • Most cultures celebrate the birthday of a "Leaper" on the 28th February, so a Leaper born on 2012-02-29 would celebrate their first birthday on 2013-02-28 and their

    second on 2014-02-28.

    its the law in the UK that any person who was born on 29 February in a leap year will attain the age of 18 on 1 March in a non-leap year for the purpose of voting

    http://www.electoralcommission.org.uk/__data/assets/pdf_file/0007/43954/Part-B-Entitlement-to-register-March-2010.pdf

  • dave.ott 20779 (12/2/2014)


    I've always liked this to get the age in years

    select cast(datediff(d,@dob,getdate())/365.25 as int)

    This has problems on birthdays. As listed, the code returns 0 when dob = '12/2/2013' and getdate() = '12/2/2014'

    Don Simpson



    I'm not sure about Heisenberg.

  • Yes there is an issue if the age is under 4 years.

    I have used the prior version for adults and it works great, but for handling all birthdays

    select cast(datediff(d,@dob,getdate())/case when datediff(yy,@dob,getdate())<4 then 365 else 365.25 end as int)

  • joseph.connor (12/2/2014)


    Neat, although isn't the Floor function obsolete, since you're performing an Integer Division?

    well spotted, I was guarding against it rounding up where fractional part was > 0.5, but integer division always truncates so you are right that the floor is not needed

    http://msdn.microsoft.com/en-us/library/ms175009.aspx

    If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

  • Great....Thanks for giving the nice input.....

  • I find this works better than a scalar function...

    CREATE FUNCTION dbo.AgeInYears

    (

    @BirthDate DATETIME2,

    @AsOfDate DATETIME2

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT DATEDIFF(YEAR , @BirthDate , @AsOfDate) -

    CASE WHEN DATEPART(DY, @AsOfDate) < DATEPART(DY, @BirthDate) THEN 1

    ELSE 0

    END AS Age

    );

    Then a simple CROSS APPLY in the query...

    SELECT I.Name, I.Birthdate, A.Age

    FROM Individual AS I

    CROSS APPLY AgeInYears(I.Birthdate, CURRENT_TIMESTAMP) AS A

    WHERE A.Age >= 18;

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

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