Age-range query

[Ed MacAlmon]

I'm working with MSDE in an Access .adp project and trying to create a
view that will specify an age range run against GETDATE() so that I can
return various groups of children within a specific age range, say 3 - 5
year-olds. I have this code in my Select statement (0 +
CONVERT(char(8),GETDATE(), 112) - CONVERT(char(8), dbo.T_PERSON.DOB,
112)) / 10000 AS Age which seems to report the child's date of birth in
years and am trying to use the following in the Where section to limit
the return to the age range I'm looking for. (dbo.T_PERSON.DOB BETWEEN
GETDATE - (0 + CONVERT(char(8), GETDATE(), 112)- CONVERT(char(8),
dbo.T_PERSON.DOB, 112)) / 10000 - 3) AND GETDATE - (0 + CONVERT(char(8),
GETDATE(), 112) - CONVERT(char(8),dbo.T_PERSON.DOB, 112)) / 10000 - 5)

It doesn't work. Can someone tell me if this is off the wall, just needs
a tweak, or there is a better way to do this. Thanks for any help. --Ed

 

[Duane Hookom]

I did a quick search on Google for Calculate Age SQL Server Function and
found a function at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=772&lngWId=5.
You should be able to create groups from the returned age values.

 

[Ed MacAlmon]

I did a quick search on Google for Calculate Age SQL Server Function and
found a function at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=772&lngWId=5.
You should be able to create groups from the returned age values.

Thanks for the response, Duane. Unfortunately, the reference is over my
head in terms of applying it within my query, so I'll keep looking. --Ed

 

[Brian Camire]

I haven't tested this, but you might try a criteria like:

BETWEEN DATEADD(year, -5, GETDATE()) AND DATEADD(year, -3, GETDATE())

on dbo.T_PERSON.DOB.

 

[Ed MacAlmon]

I haven't tested this, but you might try a criteria like:

BETWEEN DATEADD(year, -5, GETDATE()) AND DATEADD(year, -3, GETDATE())

on dbo.T_PERSON.DOB.

Thanks for help, Brian. It took a bit of tweaking, but you put me on
the right track and I got it! Thanks very much. --Ed