Got this nearly worked out now, but somehow I can't get the nested case when syntax right:
SELECT case when date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) || ' years') <= date('now') then case when strftime('%d', 'now') > strftime('%d', '2006-10-14') then ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 + (strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1 else (strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 + (strftime('%m', 'now') - strftime('%m', '2006-10-14')) end else case when strftime('%d', 'now') > strftime('%d', '2006-10-14') then ((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 + (strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14')))) -1 else (strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 + (strftime('%m', 'now') + (12 - strftime('%m', '2006-10-14'))) end end It will give me an error (from my VB wrapper) syntax error near else. Any idea what is wrong here? RBS -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 31 May 2007 22:17 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on literal RB Smissaert wrote: > Thanks to Dennis Cote I got a nice way to get the age from the date in the > form 'yyyy-nmm-dd'. It works fine when I run it on a field, but when I run > it on a literal date it gives me 100 too much: > > select > case when > date('2002-01-01', '+' || (strftime('%Y', 'now') - > strftime('%Y', '2002-01-01')) || ' years') <= date('now') > then > strftime('%Y', 'now') - strftime('%Y', '2002-01-01') > else > strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 > end > > Why is this? > > This works for me in the sqlite shell as shown below: C:\Documents and Settings\DennisC>sqlite3 SQLite version 3.3.15 Enter ".help" for instructions sqlite> sqlite> select ...> case when ...> date('2002-01-01', '+' || (strftime('%Y', 'now') - ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now') ...> then ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') ...> else ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 ...> end ...> ; 5 How are you running this query? Dennis Cote ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------