Can confirm now that mine didn't work and yours does. Nice work! RBS
-----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 22 December 2006 19:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] calculate age RB Smissaert wrote: > 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. > > > This is either clever or foolhardy depending upon how you look at it. It works only because sqlite stops at the first illegal character (the -) when converting a string to an integer as it tries to subtract the two date stings. (date('now') - '2002-12-22') becomes '2006-12-22' - '2002-12-22' which is converted to 2006 - 2002 without any warning or error and produces the correct result 4 Other than that it uses the reverse logic of my case statement. Get the difference of the years and subtract 1 or 0 depending upon the actual date. Dennis Cote ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
