Age calculation in years, months, days

Question

The following code returns the age from a particular database.

What do I add to get the exact date in years, months, days?

<%= DateDiff("yyyy",rs("Dateofbirth"),date)%>

i.e. result should be 12 yrs 6 months 8 days old.

You think doing the calculation outside SQL Server is going to be more accurate, it's still `DateDiff()` and works in exactly the same way. Follow-up question from [Age not Returned When Executing Query](http://stackoverflow.com/q/34174145/692942). — user692942, Dec 10 '15 at 09:36
Related. [How to calculate age in T-SQL with years, months, and days](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) — user692942, Dec 10 '15 at 09:37
actually it does bring out the age. i did run it and got an approximate age,though not accurate — eug, Dec 10 '15 at 11:45

score 4 · Answer 1 · answered Dec 10 '15 at 10:34

DateDiff("d",rs("Dateofbirth"),date) will give you a number of days. DateDiff("m",rs("Dateofbirth"),date) will give you a number of months.

So, something like(I don't know exactly what is rs()) :

CurrentDate = rs("Dateofbirth")
Years = DateDiff("yyyy", CurrentDate ,date)
ThisYear = DateAdd("yyyy", Years, CurrentDate)
Months = DateDiff("m", ThisYear ,date)
ThisMonth = DateAdd("m", Months, CurrentDate)
Days = DateDiff("d", ThisMonth, date)
Age = CStr(Years) & " years" & CStr(Months) & " months" & CStr(Days) & Days

But after some toying, it didn't always work. The difference is somewhat rounded up. So I had negative numbers for days or months, in some occurences. So I went mad and overprotected the code :

MsgBox(Age("09-12-1946"))
Function Age(DateOfBirth)
    Dim CurrentDate, Years, ThisYear, Months, ThisMonth, Days
    CurrentDate = CDate(DateOfBirth)
    Years = DateDiff("yyyy", CurrentDate, Date)
    ThisYear = DateAdd("yyyy", Years, CurrentDate)
    Months = DateDiff("m", ThisYear, Date)
    ThisMonth = DateAdd("m", Months, ThisYear)
    Days = DateDiff("d", ThisMonth, Date)

    Do While (Days < 0) Or (Months < 0)
        If Days < 0 Then
            Months = Months - 1
            ThisMonth = DateAdd("m", Months, ThisYear)
            Days = DateDiff("d", ThisMonth, Date)
        End If
        If Months < 0 Then
            Years = Years - 1
            ThisYear = DateAdd("yyyy", Years, CurrentDate)
            Months = DateDiff("m", ThisYear, Date)
            ThisMonth = DateAdd("m", Months, ThisYear)
            Days = DateDiff("d", ThisMonth, Date)
        End If
    Loop
    Age = Years & "y/" & Months & "m/" & Days
End Function

It's probably excessively defensive code(with many repeated lines, bad bad bad), but it works. I leave you making it prettier. Or ask on Code Review for a nicer code.

I have tried to shorten the code to this: an improved version: function getAge(DateOfBirth) age = DateDiff(yyyy,DateOfBirth,Getdate()) If date() < DateSerial(Year(date()), Month(dateofbirth), Day(dateofbirth)) Then age = age - 1 End If getAge =age end function — eug, Dec 10 '15 at 11:57

Paul · Answer 2 · 2015-12-10T10:31:39.650

1

Perhaps the quickest and simplest way is to divide the date difference by 365.25, like so:

Dim age, dob
dob = CDate("01-Jan-1980")
age = (Now() - dob) / 365.2425

Though it's not exactly accurate, it's most likely accurate enough for 99 ~~.99~~% of usage cases (i.e. where you don't need to calculate to the exact second).

edited Dec 10 '15 at 10:31

answered Dec 10 '15 at 08:52

Paul

4,160
3
30
56

I'm not sure it's quite as high as 99.99%, basically the closer `Now()` is to the birthday the less accurate it becomes. – user692942 Dec 10 '15 at 09:38
To a maximum of about a day. – Paul Dec 10 '15 at 09:39

score 1 · Answer 3 · edited May 23 '17 at 12:33

Based on Age calculations in the other questions come up with this for a more accurate age check.

Dim Age, CurrentDate, BirthDate

CurrentDate = Now()
BirthDate = CDate(rs("Dateofbirth") & "")

'Convert to yyymmdd
CurrentDate = Year(CurrentDate) & Right("00" & Month(CurrentDate), 2) & Right("00" & Day(CurrentDate), 2)
BirthDate = Year(BirthDate ) & Right("00" & Month(BirthDate ), 2) & Right("00" & Day(BirthDate ), 2)
Age = Fix((0 + CurrentDate - BirthDate) / 10000)

Response.Write Age

^{Based on @dotjoe's answer from How to calculate age (in years) based on Date of Birth and getDate()}

score 1 · Answer 4 · edited Mar 27 '23 at 11:15

this the final answer for the specific code

function getAge(DateOfBirth)
    'get age in  years
     ageyears = DateDiff("yyyy",DateOfBirth,date())
    'get month of dob
     BirthMonth = Month(DateOfBirth)
    'get todays month
     CurrMonth = Month(date)
    'get day of dob
     ageDay = Day(Date)

    If  CurrMonth< BirthMonth Then
        Agemonth= 12 + CurrMonth - BirthMonth
    else
        agemonth = CurrMonth - BirthMonth
    end if

    Age= ageyears&"years "&ageMonth&" months "&ageDay& "days"
    getAge  =Age
    
end function

Thanks all for your help

score 1 · Answer 5 · answered Oct 11 '16 at 20:14

Try the below code:

' Usage:
' GetDiff(date1, date2, accuracy)
' date1 - first date
' date2 - second date
' accuracy - output format: from 1 (N years) to 6 (N years, N months, N days, N hours, N minutes, N seconds)

MsgBox GetDiff(CDate("09-11-2014 10:55:30"), CDate("08-10-2016 15:45:10"), 6)

Function GetDiff(d1, d2, a)
    Dim r
    ReDim r(a - 1)
    Delta 0, Array("yyyy", "m", "d", "h", "n", "s"), Array("years", "months", "days", "hours", "minutes", "seconds"), r, d1, d2, False
    GetDiff = Join(r, ", ")
End Function

Sub Delta(i, t, n, r, d1, d2, c)
    Dim q, d
    q = DateDiff(t(i), d1, d2)
    If UBound(r) > i Then
        Do
            d = DateAdd(t(i), q, d1)
            Delta i + 1, t, n, r, d, d2, c
            If c Then Exit Do
            q = q - 1
        Loop
    End If
    c = q >= 0
    r(i) = q & " " & n(i)
End Sub

Age calculation in years, months, days

5 Answers5

Linked

Related