Craig Smith wrote:
> 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;
Why the nested select? Why not just
round((julianday('now') - julianday(birth))/365,1)
> 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.
That would depend on when in the year the person was born. Your
condition "date ('now') - birth > 12" subtracts years while ignoring
months and days (the minus operator implicitly converts strings to
integers, and the conversion stops at the first non-digit character). So
the condition would be almost correct for a person born in December, but
will be one year off for someone born in January.
> However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12.
This condition is simply always true (strings compare greater than
integers, regardless of actual values). I predict if you drop it
completely you'll get the same results.
> Is there
> any way I can make the SELECT more precise?
Try this:
WHERE ( date('now') - birth - (substr(date('now'), 6) < substr(birth,
6)) )
BETWEEN 12 and 24
This expression calculates full years of age (in other words, it
increases by one each year on the person's birthday).
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
