Nando <[email protected]> wrote:
> SQLite system for working with dates is very powerful, but I find it
> strange. I'm trying to select the dates between the beginning and end of a
> week defined from the date of a given day. If I understand the documentation
> thing this code should work:
>
>
> select * from TABLE where DATE between date('now','-7 days','Weekday 0')
> and date('now','-7 days','Weekday 6');
Today is Saturday, 10/15. date('now','-7 days') is the last Saturday, 10/8.
date('now','-7 days','Weekday 0') is the nearest Sunday on or after 10/8 - that
is, the Sunday of 10/9. Similarly, date('now','-7 days','Weekday 6') is the
nearest Saturday on or after 10/8 - that is, 10/8 itself. Since 10/9 is greater
than 10/8, there are no dates that fall between them.
This inversion would hapeen every day except on a Sunday.
You might want something like this:
between date('now','Weekday 0', '-7 days') and date('now', 'Weekday 0', '-1
days')
That's always the nearest previous Sunday through the following Saturday (which
may be in the past or in the future). Adjust to taste.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
