sqLite database -next week, next Month query

Question

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 

Answer 1

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.