CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Ori Cohen
Ori Cohen

Reputation: 19

SQLite filter records by month/year

I used the dumpCursor() method to verify if the db contains the proper values
But when I run:

myDB.rawQuery("SELECT * FROM " + FeedEntry.TABLE_NAME +" WHERE YEAR(date) = 2015", null);

(the date column is defined as "datetime DEFAULT NULL")
I get an uncaught exception.

What am I doing wrong?

Upvotes: 1

Views: 7174

Answers (2)

Phantômaxx
Phantômaxx

Reputation: 38098

I answered a similar question yesterday: https://stackoverflow.com/a/33657233/2649012

The key is using the strftime() function.
The parameter to pass is '%m' for a monthly search or '%Y' for a yearly search.

Therefore, change your query to

myDB.rawQuery("SELECT * FROM " + FeedEntry.TABLE_NAME +" WHERE strfttime('%Y', date) = '2015'", null);

Upvotes: 3

Karakuri
Karakuri

Reputation: 38595

SQLite does not have the YEAR function found in other flavors of SQL. The available date & time functions are listed on this page.

Depending on how your date are stored, you may have to do something like simple substring extraction (using the SUBSTR function) on your column value, for example.

Upvotes: 0

Related Questions