Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools WHERE julianday('now') -
> julianday(arrival_date) > 7
>
Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', '-7 days');
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
>
Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
>
> WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
>
All answers above are correct, as far as I can see at a quick
glance. But here is an efficiency tip: You can move the
constant date calculations into a subquery and thereby only
evaluate them once for the whole statement instead of once
for each row of result. For example:
WHERE (SELECT julianday('now')) - julianday(arrival_day) > 7
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
WHERE (SELECT julianday(date('now'))) - julianday(date(arrival_date))>7
This is a dirty trick and it does make the query more difficult
to read, so only use it if it is necessary for performance.
--
D. Richard Hipp <[EMAIL PROTECTED]>
