Calculating an age 'in the next month'

Databases
#1

I want to be able to find all the people in my database who turn the age of 65 in the next month (or x days if easier). I can find all those over 65 with the below MySQL query, but it returns everyone who is over 65 and have already been ‘dealt with’. I want to be able to narrow it in MySQL. The other alternative of course is that I could just do a second calculation in Xojo and calculate each record individually and decide whether to display it but that seems like a ‘fudge’ and I really ought to let the database do the work. Suggestions or pointers welcome as Google got me this far :slight_smile:

select Number, LastName, FirstName, DateOfBirth from names where (DATE_FORMAT(now(), '%Y') - DATE_FORMAT(fldDateOfBirth, '%Y') - (DATE_FORMAT(now(), '00-%m-%d') < DATE_FORMAT(fldDateOfBirth, '00-%m-%d')) >64) ORDER BY DateOfBirth DESC

#2

(untested) how about something like

select Number, LastName, FirstName, DateOfBirth from names where (julianday(now()) + 31 - julianday(fldDateOfBirth) ) > 65 * 365.25 and (julianday(now()) -1 - julianday(fldDateOfBirth) ) < 65 * 365.25 ;
basically, if they are over 65 in 31 days time, and less than 65 yesterday?

#3

try something like this:

select Number, LastName, FirstName, DateOfBirth from names where date_add(DateOfBirth, interval 65 year) between now() and date_add(now(), interval 1 month)

#4

why not just pre-calculate the required birthdate and use that in your select statement instead of trying to do a bunch of fancy compares and multiplcation etc.

dim birthfor65 as new date
birthfor65.day=1
birthfor65.month=birthfor65.month+1
birthfor65.year=birth65.year-65
sql="Select * from names where fldDateofBirth<='"+birthfor65.sqldate+"'"
#5

[quote=206012:@Dave S]why not just pre-calculate the required birthdate and use that in your select statement instead of trying to do a bunch of fancy compares and multiplcation etc.

dim birthfor65 as new date birthfor65.day=1 birthfor65.month=birthfor65.month+1 birthfor65.year=birth65.year-65 sql="Select * from names where fldDateofBirth<='"+birthfor65.sqldate+"'" [/quote]
add on a “and >= now()” otherwise you get everyone who is already 65

#6

=now() is everyone not yet born

Birthfor65 would be “01Sep1950” so anyone born on or before that date is 65 or will be by next month

#7

Ah yeah I see what I misread

#8

But the results in the month after that will include the same people.

What Paul wants is people who will become 65 during the next month: people who are 64 now, and who will be 65 within the next <31 days> /

#9

Thanks for the responses, I really like the simplicity and control of the dates with Dave’s response but the sql Giulio provided nails it and the work is being done by the server so is the solution that works for me in this case.

#10