CODEWITHSUNDEEP
androidsqliteandroid-datepicker
Tartar
Tartar

Reputation: 5452

Android: SQLite Getting all records that inserted in last 7 days

Cursor cursor = db.rawQuery("SELECT * FROM expenses WHERE expense_date > (SELECT DATE('now', '-7 day'));", null);

I have this above query to get all records that inserted in last 7 days. But as a result of this query i am getting all the records. The date column of these records is inserted via Android's DatePicker and they are in 30-Jul-2015 format. I guess that's why this query does not work properly. Is there a way to make them consistent to get this query work properly or i am missing out something else ? Any help would be appreciated.

Upvotes: 0

Views: 1173

Answers (1)

Barak
Barak

Reputation: 16393

I've never tried using the SQLite built in date functions. Taking a quick look at the SQLite date function documentation here, it would seem that to use them the date needs to be in the format YYYY-MM-DD, which would be why your query is failing.

You can do some string manipulation/formatting before saving to the db and use that format.

Personally, I usually format in that manner but without the dash separators, then a simple less than/greater than comparison can be used, treating the date like a regular number. Of course some conversion back and forth is necessary for display, but for under the hood it works great.

int now = 20150730;
String query = "SELECT * FROM expenses WHERE expense_date > '" + now-7 + "'";

Upvotes: 1

Related Questions