Calculate age from DOB - Miscalculation

[vinamr]

Hi,

I am using the below code to calculate the age of a child from DOB. Criteria is as of 09/01/2010 (for the school year 2010-11). Even though the script seems to working fine in some cases the age calculations are wrong. Can some please tell what is wrong with the code.

For ex: DOB 12/20/1999 the age should be 10 as if 09/01/2010. However the age is calculated as 11 years.

Function Row_Inserting(rs)
If Not EW_DEBUG_ENABLED Then On Error Resume Next
DIM ChildAge, fromDate
fromDate=CDate("09/01/2010")
ChildAge = DateDiff("d",rs("DOB"),fromDate)
If ChildAge <= (Cint("1095")) Then
CancelMessage="Your child is too young to attend our classes. Please register after your child has reached pre-K."
Row_Inserting = False
Else
rs("Age")= DateDiff("YYYY",rs("DOB"),Date())
Row_Inserting = True
End If
End Function

Any pointers .. Thanks
Vinny

[abduraooft]

In my php page, I use $age = date('Y') - date('Y', strtotime($dob)); // $dob in 'YYYY-MM-DD' format
if (date('md') < date('md', strtotime($dob))) {
$age--;
}

[vinamr]

Thanks Abdu,

I am using this in classic ASP. Not sure if I could use your exact code. Any suggestions. Thanks.

Vinny

[Old Pedant]

Function AgeAsOf( DOB, AsOf )
age = DateDiff("yyyy",DOB,AsOf)
If Month(DOB) > Month(AsOf) Then
age = age - 1
ElseIf Month(DOB) = Month(AsOf) AND Day(DOB) > Day(AsOf) Then
age = age - 1
End If
AgeAsOf = age
End Function

Function Row_Inserting(rs)
DIM childAge
ChildAge = AgeAsOf( rs("DOB"), #9/1/2010# )
If ChildAge < 5 Then
CancelMessage = "Your child is too young to attend our classes. " _
& "Please register after your child has reached pre-K."
Row_Inserting = False
Else
rs("Age") = ChildAge
Row_Inserting = True
End If
End Function

[Old Pedant]

This would be closer to what Abduraooft showed, I guess:

Function AgeAsOf( DOB, AsOf )
age = DateDiff("yyyy",DOB,AsOf)
If (Month(DOB)*100+Day(DOB)) > (Month(AsOf)*100+Day(AsOf)) Then
age = age - 1
End If
AgeAsOf = age
End Function

But it's the same answer.

[vinamr]

Hi Old Pedant,

How can I combine your code into one function instead of two? Just like the one I had earlier. Sorry I tried a few things but it didn't work for me.

Thanks in advance.

Vinny

[Old Pedant]

How can I combine your code into one function instead of two?

Why does it matter??? What's wrong with adding another function???

But it's trivial. Just write the same code in-line:

Function Row_Inserting(rs)
DIM childAge, DOB
CONST AsOf = #9/1/2010#
DOB = rs("DOB")
' and then the same code as the function:
childAge = DateDiff("yyyy", DOB, AsOf)
If Month(DOB) > Month(AsOf) Then
childAge = childAge - 1
ElseIf Month(DOB) = Month(AsOf) AND Day(DOB) > Day(AsOf) Then
childAge = childAge - 1
End If
' end of same code as function
If childAge < 5 Then
CancelMessage = "Your child is too young to attend our classes. " _
& "Please register after your child has reached pre-K."
Row_Inserting = False
Else
rs("Age") = childAge
Row_Inserting = True
End If
End Function

[vinamr]

Thank you OP,

That worked absolutely fine. I really appreciate your help. I also copied the same code under row_updating event.

Regards
Vinny

[vinamr]

Hi OP,

One other question .. do you know a way to update data in a SQL server table using the above function. The previous function had captured wrong age calculations in the AGE field/column. Was wondering if there is a way to update all existing records in the table. The new ones are working fine after using your code.

Any pointers. Thanks
Vinny

[Old Pedant]

Do it all in SQL Server. No ASP needed.


UPDATE tablename
SET age = ( CASE WHEN Month(DOB)*100+Day(DOB) > 901
THEN DateDiff(yyyy, DOB, '9/1/2010') - 1
ELSE DateDiff(yyyy, DOB, '9/1/2010') END )


Untested, but I think it's right.

You could make sure it's right by running a SELECT query test, first:

SELECT DOB, ( CASE WHEN Month(DOB)*100+Day(DOB) > 901
THEN DateDiff(yyyy, DOB, '9/1/2010') - 1
ELSE DateDiff(yyyy, DOB, '9/1/2010') END ) AS age
FROM tablename

verify that gets the right answers and, if so, run the UPDATE.

I hardcode 901 since that's MONTH('9/1/2010')*100+DAY('9/1/2010') and seemed silly to do the calculation.

OH! Or can code all that a bit more simply, since we are hard-coding the 9/1/2010 date:

SELECT DOB, ( CASE WHEN Month(DOB)*100+Day(DOB) > 901
THEN 2010 - Year(DOB) - 1
ELSE 2010 - Year(DOB) END ) AS age
FROM tablename

and then, assuming it's right:

UPDATE tablename
SET age = ( CASE WHEN Month(DOB)*100+Day(DOB) > 901
THEN 2010 - Year(DOB) - 1
ELSE 2010 - Year(DOB) END )
FROM tablename

[vinamr]

OP,

THANK YOU .. you are just awesome. :thumbsup:

I ran the first query and tested the data on a test table. Worked perfectly. Will run it on a few more records to double check.

I only need to run this once on the existing table to correct the Age. The new function will take care of all new entries.

Thanks again OP. Much appreciated.

Regards
Vinny

[DustinHoffman10]

I think this will help you.
struct Age
{
public int days;
public int months;
public int years;

public override String ToString()
{
return years + " Years " + months + " Months " + days + " days";
}
}

class Program
{
static void Main()
{
DateTime age1 = new DateTime(1985, 4, 3);
DateTime age2 = new DateTime(1985, 4, 8);
DateTime age3 = new DateTime(1985, 10, 3);
DateTime age4 = new DateTime(1985, 10, 8);

DateTime age5 = new DateTime(1985, 8, 5);
DateTime age6 = new DateTime(1985, 8, 6);
DateTime age7 = new DateTime(1985, 8, 4);

DateTime asOnDate = new DateTime(2010, 8, 5);

Program p = new Program();

Console.WriteLine(p.FindAge(asOnDate, age1));
Console.WriteLine(p.FindAge(asOnDate, age2));
Console.WriteLine(p.FindAge(asOnDate, age3));
Console.WriteLine(p.FindAge(asOnDate, age4));
Console.WriteLine(p.FindAge(asOnDate, age5));
Console.WriteLine(p.FindAge(asOnDate, age6));
Console.WriteLine(p.FindAge(asOnDate, age7));
}

public Age FindAge(DateTime asOnDate, DateTime dob)
{
Age age = new Age();

DateTime lastAnniversary = new DateTime(asOnDate.Year, dob.Month, dob.Day);

if (lastAnniversary <= asOnDate)
{
age.years = lastAnniversary.Year - dob.Year;

if (lastAnniversary.Day <= asOnDate.Day)
{
age.months = asOnDate.Month - lastAnniversary.Month;
age.days = asOnDate.Day - lastAnniversary.Day;
}
else if(lastAnniversary.Day > asOnDate.Day)
{
age.months = asOnDate.Month - lastAnniversary.Month - 1;
int lastDayOfLastMonth = (new DateTime(lastAnniversary.Year, asOnDate.Month - 1, 1)).AddMonths(1).AddDays(-1).Day;
age.days = lastDayOfLastMonth - lastAnniversary.Day;
age.days += asOnDate.Day;
}
}
else if (lastAnniversary > asOnDate)
{
lastAnniversary = lastAnniversary.AddYears(-1);

age.years = lastAnniversary.Year - dob.Year;

if (lastAnniversary.Day <= asOnDate.Day)
{
age.months = 12 - lastAnniversary.Month;
age.months += asOnDate.Month;

age.days = asOnDate.Day - lastAnniversary.Day;
}
else if (lastAnniversary.Day > asOnDate.Day)
{
age.months = 12 - lastAnniversary.Month;
age.months += asOnDate.Month - 1;

int lastDayOfLastMonth = (new DateTime(lastAnniversary.Year, asOnDate.Month - 1, 1)).AddMonths(1).AddDays(-1).Day;
age.days = lastDayOfLastMonth - lastAnniversary.Day;
age.days += asOnDate.Day;
}
}

return age;
}
}

[Old Pedant]

I think this will help you.


Let's see... It's not either VBScript or JScript code, and those are the only two languages usable with ASP. And it's clearly not T-SQL code. So how will that help him with either an ASP or SQL Server solution?

What language is that, anyway? C#??