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