On Sep 16, 2009, at 11:49 PM, Craig Smith 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;
OK, let's see. "date('now')" is a string in the format 'YYYY-MM-DD'
and "birth" is another string in the same format. When you try to
subtract one string from the other, SQLite tries to convert the
strings into numbers as best it can. In this case, it just extracts
the YYYY part as an integer.
So '2009-09-17' - '1997-12-12' is going to equal 12, not 11.77 as you
seemed to be expecting. And '2009-09-17' - '1997-01-01' will also
give 12, not 12.71.
Have you tried this instead:
... WHERE date('now') > date(birth, '+12 years') AND date('now')
< date('birth',+24 years') ...
>
> 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
D. Richard Hipp
d...@hwaci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
