That's because today is Saturday. So 'Weekday 6' is Saturday which is the same
as 'now' - 7 days. No date advance is done.
sqlite> select date('now','-7 days','Weekday 0');
2011-10-09
sqlite> select date('now','-7 days','Weekday 1');
2011-10-10
sqlite> select date('now','-7 days','Weekday 2');
2011-10-11
sqlite> select date('now','-7 days','Weekday 3');
2011-10-12
sqlite> select date('now','-7 days','Weekday 4');
2011-10-13
sqlite> select date('now','-7 days','Weekday 5');
2011-10-14
sqlite> select date('now','-7 days','Weekday 6');
2011-10-08
But if you do -6 days it behaves as expected...but only because today is
Saturday again. It won't work correctly tomorrow.
sqlite> select date('now','-6 days','Weekday 0');
2011-10-09
sqlite> select date('now','-6 days','Weekday 1');
2011-10-10
sqlite> select date('now','-6 days','Weekday 2');
2011-10-11
sqlite> select date('now','-6 days','Weekday 3');
2011-10-12
sqlite> select date('now','-6 days','Weekday 4');
2011-10-13
sqlite> select date('now','-6 days','Weekday 5');
2011-10-14
sqlite> select date('now','-6 days','Weekday 6');
2011-10-15
If you want just the last week do this:
sqlite> create table t(d date);
sqlite> insert into t values('2011-10-08');
sqlite> insert into t values('2011-10-09');
sqlite> insert into t values('2011-10-10');
sqlite> insert into t values('2011-10-11');
sqlite> insert into t values('2011-10-12');
sqlite> insert into t values('2011-10-13');
sqlite> insert into t values('2011-10-14');
sqlite> insert into t values('2011-10-15');
sqlite> select * from t where d between date('now','-6 days') and date('now');
2011-10-09
2011-10-10
2011-10-11
2011-10-12
2011-10-13
2011-10-14
2011-10-15
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: [email protected] [[email protected]] on
behalf of Nando [[email protected]]
Sent: Saturday, October 15, 2011 4:17 PM
To: [email protected]
Subject: EXT :[sqlite] Time and date functions
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');
But it does not work, although SQLite does not show any syntax error so do
not know where I'm wrong.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
