Age Calculation

[dwarvenmilitia]

Hi...

I have a table with DOB and I need to calculate and age so i can search for a age range. I know how to search once I have an age but not to sure how to calc age from retrieved table values.

I've read people use vba but I have no knowledge of this. This is being down in ASP too.

Any help would be appreciated.

 

[Sheco]

Suppose you want people that are in the range of 30 to 35 years old... so the birth was between 30 and 35 years ago.

Date1 = DateDiff("yyyy", -35, Now)
Date2 = DateDiff("yyyy", -30, Now)

Select People From Table Where DOB between Date1 and Date2

 

[Tarwn]

Except I think DateDiff gives you round-off errors.
Best bet, if you have a birthdate, is to subtract the minimum and max ages from todays date and check for birthdates in that range.

-T

 

[dwarvenmilitia]

Hey. Sheco I can see where you're going with that. I was however going to use this variation of code.

if request("agemin") <> "" then
strSQL = strSQL & " and age between " & request("agemin") & " and " & request("agemax") & "
end if

I needed an age calc also but is it still better you reakon to use DateDiff?

I thought of doing that before but that might be a better way.

Technically it should matter if it is out by a year but again this is and age range over many people and knowing that the user searching will probably want ages in the middle, it won't really matter for accuracy.

Is there a way to resolve the rounding in datediff that doesn't make the sql query to complex?

 

[dwarvenmilitia]

Tarwn...

How would you go about that?

You mean like

top range = date() - maxage
low range = date() - minage

select.... where "search age input" between top and low

Is this roughly what you mean? Pseudo code obviously.

 

[DotNetGnat]

thread183-778001
thread183-1098667

-DNG

 

[Sheco]

Well cr@p my brain was thinking DateAdd() but my fingers typed DateDiff().... I even passed it the DateAdd parameters... sheesh im going crazy!

DataAdd(units, number, date)
DateDiff(units, firstDate, secondDate)

 

[dwarvenmilitia]

Hey Sheco... Just add to this I'm new at most of this!

I understand datediff but how does dateadd fit into the calc/sql query?

I haven't written any code as such but it will be roughly like this.

Dim varagemin = Request.Form("agemin")
Dim varagemax = Request.Form("agemax")
Dim date1 = DateDiff("yyyy", -varagemin, Now)
Dim date2 = DateDiff("yyyy", -varagemax, Now)

SELECT ... FROM... WHERE (I have a series of If's here to add to SQL where from checkboxes and text input etc if values are entered but anyway)

WHERE DOB BETWEEN date1 AND date2

But how would I change this for months and years? Days are pushing it but I don't think it necessary for now, just the months are needed.

Would it go along the lines adding underneath date1

Dim date3= DateDiff("m", -varagemin, Now) - (date1*12)
Dim date4= DateDiff("m", -varagemax, Now) - (date2*12)

SQL = SELECT...... WHERE DOB BETWEEN date3 AND date4

 

[johnwm]

DateAdd also takes negative arguments.

date1 = DateAdd("yyyy", -varagemin, Now)
date2 = DateAdd("yyyy", -varagemax, Now)
SELECT ... FROM... 
WHERE DOB BETWEEN date2 AND date1

Note Date2 is earlier than Date1

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Sheco]

Below is a copy of my earlier post except with DateAdd() as I originally intended but mis-typed.... see that Date1 is actually the older date:

Suppose you want people that are in the range of 30 to 35 years old... so the birth was between 30 and 35 years ago.

Date1 = DateAdd("yyyy", -35, Now)
Date2 = DateAdd("yyyy", -30, Now)

Select People From Table Where DOB between Date1 and Date2

-----------------------------------
But how would I change this for months and years?
It depends on your input...

Suppose this is for a child-care business and the range is 6 to 48 months... well that is easy just multiply by negative one and use months instead of years as your units.

If the input looks like this: 9.5 years to 10.99 years then you could just multiply by -12 and use months as the units.

If the input looks like this: 9 years 6 months to 10 years 11 months then something like this:
Date1 = DateAdd("m", -1 * ((10 * 12)+ 11), Now)
Date2 = DateAdd("m", -1 * ((9 * 12) + 6), Now)
Select People From Table Where DOB between Date1 and Date2

 

[dwarvenmilitia]

Hey Sheco thats awesome!

Thanks for that. Even if I say 0 months it still makes it more accurate (the last one).

Thanks for all the help guys.

__________________________________
Two blondes walk into a building... You think one of them would have seen it!