SQLite query question

Question

I have 1 table:

table tb1 (
    _id integer primary key autoincrement
    ,busnum text not null
    , servdate date not null
); 

I need a query which will get me all entries which have a "servdate" for the current week (the week starts on monday.)

So for example:

if I run the query on wednesday the 24th novemeber, it will get me all entries for monday 22nd, tuesday 23rd and wednesday 24th.

If I ran the query on sunday 28th, it will get me all entries for the full week (mon - sun), starting monday 22nd - sunday 28th.

If I run the query on monday, then it will just get me all entries for that day.

Thanks in advance. (thanks to admin for formatting my question)

Answer 1

Use the modifier weekday 1 (from the date time functions documentation) :

WHERE servdate BETWEEN date('now', 'Weekday 1', '-7 days') AND date('now')