SQL Server calculate the exact age on the day

Posted on 2006-04-27
Last Modified: 2012-05-05
Hi, I have a date field in MS SQL Server with the name "mDOB" with the datatype "datetime" applied to it.

this dateofbirth is  in that field "05.09.1973"

I need to calculate the exact age and get it into flash.

This is what I do, it works but it misses on up to 10 days, and that is not acceptable.

Colud you please help with a solution?

Response.write "mAge=" & (DateDiff("yyyy", objRS("mDOB"), Now()))& "&"
Question by:oksum73
    LVL 4

    Expert Comment

    Why missed 10 days? We just simply subtract this year to birth year, then we have the result !!! Can you explain in detail?
    LVL 4

    Accepted Solution

    Your result fail to pleasure you maybe because your Date and the Date today are not in the same format.
    To make sure the two date is same format:
    Response.write "mAge="
    & (DateDiff("yyyy", DateFormat(objRS("mDOB"),"mm/dd/yyyy"), DateFormat(Now(),"mm/dd/yyyy"))) & "&"
    LVL 44

    Expert Comment

    when you say 'te exact age', do you mean X years, Y months and Z days as the age , or do you simply want the age in Years?

    DateDiff("yyyy".......) will simply return the difference between the two dates, in TOTAL YEARS, and at that it will be WRONG before that anniversay of your birth.

    My birthday is 10/17/1944 making me 61 years 6 months and 11 days old at the moment, but

    select DateDiff(year, '10/17/1944', getdate()) returns 62

    however if you want the precise age, in Years months and Days, you need to do some calculations in the SQL, like this:

    select cast(DateDiff(day, mDOB, getdate())/365.245 as int) as years ,cast((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day, mDOB, getdate())/365.245 as int)) * 12 as int) as months,
    cast((((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day, mDOB, getdate())/365.245 as int)) * 12) - cast((DateDiff(day, mDOB, getdate())/365.245 - cast(DateDiff(day,mDOB, getdate())/365.245 as int)) * 12 as int))* 30 as int) as days  

    this will return the correct number of Years, months and days as the 'exact' age.

    LVL 4

    Expert Comment

    Or you could just

    SELECT CAST(GETDATE()-'28-Nov-2005' AS INT)

    to get the age as a number of DAYS, and do what you like from there!
    LVL 4

    Expert Comment


    SELECT CAST(GETDATE()-'28-Nov-2005' AS DECIMAL) /365.25

    if you want it in years

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    DOS Database Program in Windows 10 5 85
    SQL Query to replace values in columns 4 69
    database design books 12 67
    MySQL 11 47
    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now