I think I got it now: select (date('now') - '2002-12-22') - ((julianday('now', '-' || (date('now') - '2002-12-22') || 'year') < julianday('2002-12-22')))
seems to work. RBS -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 22 December 2006 18:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] calculate age RB Smissaert wrote: > Is there a way to calculate the age given the 2 dates in the standard format > yyyy-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide > by 365, but that won't be accurate enough. > It would be easy to calculate the age in the application and update the > database, but I prefer to do it all in SQL if I can. > > RBS > > > > ---------------------------------------------------------------------------- - > To unsubscribe, send email to [EMAIL PROTECTED] > ---------------------------------------------------------------------------- - > > > Try this (untested). select case when date(dob, '+' || strftime('%Y', 'now') - strftime('%Y', dob) || ' years') >= date('now') then strftime('%Y', 'now') - strftime('%Y', dob) else strftime('%Y', 'now') - strftime('%Y', dob) - 1 end as age from t; HTH Dennis Cote ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------