Got this now, after correcting the brackets: 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
RBS -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 19:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Age calculation on literal On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote: > > 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 + You have an extra closing bracket in the line above. (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? Try this instead, I find the extra indentation makes it easier to see what you are doing. 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 HTH Dennis Cote ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------