I am kind of stuck in a place. I want to load data from my SQLite database in android by next week, next month, etc. But couldn't do it although I have tried last week and last month's query, which was working fine. It would be helpful if someone can assist me with the query.
For last week and last month the query I used from the following answer and which was working fine. Query last day, last week, last month SQLite
But if I want to load data for next week and next month then what will be the query? I have tried the following queries but wasn't working.
SELECT * FROM AT_ActivityTrackerEntry WHERE AT_ActivityTrackerEntry.DeadlineDate BETWEEN datetime('now', '6 days') AND datetime('now', 'localtime') limit 10
SELECT * FROM AT_ActivityTrackerEntry WHERE AT_ActivityTrackerEntry.DeadlineDate BETWEEN datetime('now', 'localtime') AND datetime('now', '+6 days') limit 10
The 1st query:
SELECT * FROM AT_ActivityTrackerEntry
WHERE AT_ActivityTrackerEntry.DeadlineDate
BETWEEN datetime('now', '6 days') AND datetime('now', 'localtime')
limit 10
is wrong because datetime('now', '6 days') is greater than datetime('now', 'localtime'), so BETWEEN fails and you get no results.
The 2nd query:
SELECT * FROM AT_ActivityTrackerEntry
WHERE AT_ActivityTrackerEntry.DeadlineDate
BETWEEN datetime('now', 'localtime') AND datetime('now', '+6 days')
limit 10
should return correct results, although you should use (or not) the modifier 'localtime' in both cases:
BETWEEN datetime('now', 'localtime') AND datetime('now', 'localtime', '+6 days')
and maybe instead of 6 you should add 7 days.
Also for the next month starting from now you can do:
BETWEEN datetime('now', 'localtime') AND datetime('now', 'localtime', '+1 month')
Note that limit without an ORDER BY clause is always a bad idea.