SQLite : obtain current week

Question

I'm trying to obtain the current week for date comparison in SQLite. I have no problem for last month, last year, today, yesterday... but don't find the solution to have the current week.

I tried lot of things like:

SELECT tastings.* FROM tastings   
WHERE (DATE(tastings.date) > DATE('now','weekday 1','+ 7 days'))

Can you help me ? Thanks.

Answer 1

This code gives you the week number where the first day of week is monday. It also works well for last and first weeks of the year.

strftime('%W', 'now', 'localtime', 'weekday 0', '-6 days')

Answer 2

I guess you want compare 2 date, Assume you have a table named _testTbl and have 3 column _id INTEGER, _name TEXT, _recordDate TEXT

you want name that record this week you can use below code:

SELECT * FROM _testTbl
WHERE _recordDate > datetime('now', 'start of day', 'weekday 6', '-7 day')

note that this week start by saturday (sunday 0, monday 1, ..., saturday 7) this t-sql means:

datetime is a sqlite date and time function.

• first parameter is given time: 'now' means the current time.
• second parameter take the time to start of day.
• third parameter take time to the next weekday number (in this case, saturday).
• fourth parameter take time to start of week