I produce a list of birthdays from within an sqlite database, and use these to populate a calender with a persons name, age in brackets after their name and place these on a calendar control on the correct date and month. No problems at all populating the calendar and retrieving the ages other than I would like to show the ‘correct age’ for past dates, today and future dates.
At the moment I use this SQL.
SELECT fldLastName, fldFirstNames, CAST ( ( julianday( 'now' ) - julianday( fldDateOfBirth ) ) / 365.25 AS integer ) AS Age,strftime( '%m', fldDateOfBirth ) AS month, strftime( '%d', fldDateOfBirth ) AS D, strftime( '%Y', fldDateOfBirth ) AS Born FROM tblnominalrole WHERE (month = strftime( '%m', '2021-04-01') or month = strftime( '%m', '2021-05-01') or month = strftime( '%m', '2021-06-06')) and fldDeletedRecord=0 ORDER BY month, D
This appears to work fine, other than ‘future dates’ where I would like to add 1 to the age, i.e. on the upcoming birthday the person will be [age shown]+1, rather than their age as of today. The records shown today and previously show the correct age, i.e. their age as of now.
If I simply add 1 to the age, then todays birthdays, and those previously give me the persons age on their next birthday which I suppose is an option and makes it consistent across the board but doesn’t feel right for ‘todays birthdays’, and those I can see on the calendar for the previous rolling two weeks where we are ‘not in next year’.
So my question is whether this is achievable direct by modifying the above SQL, or do I have to ‘fiddle the answers’ after I get them? Or even, is there an easier way?
i.e. birthday < =today then don’t add 1 to age else add 1 to age
I extract the month, and day to enable me to add the event to the calender.