Nando <fl446...@gmail.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users