Search data by week,month year and date

Question

This is my table.

CREATE TABLE Income (id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, amount DOUBLE, sdate DATE, way TEXT,desc TEXT)

And after that i want to search date sort by sdate column

this is my query but no out put from this query.

SELECT * from Income where sdate=DATE('now', 'weekday 0', '-7 days')

What I did for similar search in my app is I added another column with value as `YearMonthDay`, e.g; Today would be `20160301`. Now its easier to retrieve entries > this week's start date, this month, year (`20160101`) and use between for range. Let me know if you need more information with this approach. — midhunhk, Mar 01 '16 at 05:35

score 0 · Answer 1 · edited May 23 '17 at 11:50

This is a suggestion :

CHANGE sdate to TEXT

queries i have tried and got results accordingly;

SELECT * FROM Income WHERE sdate BETWEEN date('now', '-6 days') AND date('now', 'localtime');

SELECT * FROM Income WHERE sdate >= date('now', 'start of month') AND sdate <= date('now', 'localtime');

you can also use static date:

SELECT * FROM Income WHERE sdate >= '2016-02-01' and sdate <= '2016-03-01';

similarly all three given in this answer on stackoverflow.

Now, once you get this day, week, month you can use this variable to generate final query by using AND clause.

If you want to simply use sorted order by date than

SELECT * FROM Income WHERE sdate BETWEEN date('now', '-6 days') AND date('now', 'localtime') ORDER by sdate;

Thus, you can get result as you want.

Search data by week,month year and date

1 Answers1

This is a suggestion :