Got the syntax right, but not the logic. I believe this (VB) function will now get the right SQL to get the age in months:
Function ISO8601Date2AgeInMonths(strField As String, _ Optional strAlias As String) As String Dim strAS As String If Len(strAlias) > 0 Then strAS = " AS " End If ISO8601Date2AgeInMonths = "case when date('now') >= " & _ "date(" & strField & ", '+' || (strftime('%Y', 'now') - " & _ "strftime('%Y', " & strField & ")) || ' years') then " & _ "case when strftime('%d', 'now') < strftime('%d', " & strField & ") then " & _ "((strftime('%Y', 'now') - strftime('%Y', " & strField & ")) * 12 + " & _ "(strftime('%m', 'now') - strftime('%m', " & strField & "))) - 1 " & _ "else " & _ "((strftime('%Y', 'now') - strftime('%Y', " & strField & ")) * 12 + " & _ "(strftime('%m', 'now') - strftime('%m', " & strField & "))) - 0 " & _ "end " & _ "else " & _ "case when " & _ "strftime('%d', 'now') < strftime('%d', " & strField & ") " & _ "then " & _ "(strftime('%Y', 'now') - strftime('%Y', " & strField & ") - 1) * 12 + " & _ "(strftime('%m', 'now') + (12 - strftime('%m', " & strField & "))) - 1 " & _ "else " & _ "((strftime('%Y', 'now') - strftime('%Y', " & strField & ") - 1) * 12 + " & _ "(strftime('%m', 'now') + (12 - strftime('%m', " & strField & ")))) - 0 " & _ "End " & _ "End" & strAS & strAlias End Function RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 21:46 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Age calculation on literal 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] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------