This is a VB function I use that will give you the idea.
I think I got this with help from Dennis Cote.
Public Function AgeAtISO8601Date(ByVal strDOB As String, _
ByVal strDate As String, _
Optional ByVal strAlias As String) As String
'will make a SQLite SQL that produces the age given a:
'date of birth in ISO8601 format
'date in ISO8601 format
'-----------------------------------------------------
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
AgeAtISO8601Date = "case when date(" & strDOB & ", '+' || " & _
"(strftime('%Y', " & strDate & ")" & _
" - strftime('%Y', " & strDOB & ")) || " & _
"' years') <= " & strDate & " then " & _
"strftime('%Y', " & strDate & ")" & _
" - strftime('%Y', " & strDOB & ") " & _
"else " & _
"strftime('%Y', " & strDate & ")" & _
" - strftime('%Y', " & strDOB & ") -1 " & _
"End" & _
strAS & strAlias
End Function
RBS
On Thu, Sep 17, 2009 at 4:49 AM, Craig Smith <cr...@macscripter.net> wrote:
> Hello:
>
> I am having difficulties getting precise returns using a SELECT that
> calculates the age of persons in my table. All dates are YYYY-MM-DD,
> here is my SELECT statement:
>
> SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
> (birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date
> ('now') - birth < 24 AND married = 'Single' AND moved = 0;
>
> The problem is that anyone over the age of 12 does not show up in the
> return until their age is actually nearer to 12.5. However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12. Is there
> any way I can make the SELECT more precise?
>
> Thank you very much,
>
> Craig Smith
> cr...@macscripter.net
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
