SQL help! Age query based on future date??

[hillel]

I need a sql query that is based on how old someone will be on a future date based on their birthday.

the following formula works in Crystal:
TRUNCATE((DATE(2001,06,30) - {table.BIRTHDAYfield})/365)

but these formulas do not work in sql:

TRUNCATE(('06/30/2001' - {table.BIRTHDAYfield})/365)>=28

(('6/30/2001' - birthdayfield)/365)>=28

We've tried datediff too, but that doesn't seem to give the correct records b/c it's based on the year and not the full date.

Thanks in advance for your help

 

[foxdev]

DateDiff will work, but you need to use the 'd' parameter and then divide by 365. For an example:

[tt]declare @DateCol smalldatetime
set @DateCol = '01/01/1960'
select DateDiff(d, @DateCol, '06/30/2001')/365.25[/tt]

yields the age in years, but accurate to the day.
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org