# Age Calculation Sqlite SQL

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.

the where part of month looks odd
why not use
`WHERE month in (4,5,6)`
instead of `month = strftime( '%m', '2021-04-01') or month = strftime( '%m', '2021-05-01') or month = strftime( '%m', '2021-06-06')`

for output i would use the DateTime & DateInterval class in Xojo.
or you have to use IIF in Sql because birth month & day < now month and day

Thanks Markus, a good and neater suggestion. As the month returns ‘04’, ‘05’, ‘06’ I changed your suggestion to:

``````WHERE
month in ('04','05','06')
``````

I’ll have a play with IIF and see how I get on but I can see that getting quite fiddly.

should the month field not be better a integer field?

Possibly but strftime, returns a string formatted date and it doen’t matter for the selection purpose really, it still works and pulls the correct records.