M.Yogeshwaran M.Yogeshwaran - 1 year ago 98
Android Question

How to Retrieve value from sqlite when passing yyyy-mm to yyyy-mm-dd formated string?

Hello guys let me explain my problem in detail i have column which saves date value in yyyy-mm-dd format, Its fine until this what i need is if i pass yyyy-mm alone need to retrieve value of that whole month But i couldn't succeed in it let me post what i have tried so far:

public List<AddNoteModel>activitylist(String date){
String countQuery = "SELECT * FROM " + AddNoteModel.AddNote_Table + " where " + AddNoteModel.AddNote_ActivityDate + " LIKE ' strftime('%Y-%m'," + date +") " +"%'";
Cursor cursor = db.rawQuery(countQuery, null);
List<AddNoteModel>list=new ArrayList<>();
if(cursor!=null && cursor.getCount()>0) {
if (cursor.moveToFirst()) {
do {
String monthname=cursor.getString(0);
AddNoteModel staffModel = new AddNoteModel();
} while (cursor.moveToNext());
cursor.setNotificationUri(mcontext.getContentResolver(), DB_Timetracker_Staff);
return list;

In that date argument i will pass yyyy-mm Can anyone help me while i try the above method am getting syntax error near %.

Answer Source

No need to add "Like" here try this code instead

SELECT * FROM `your_table_name` WHERE strftime('%Y-%m', `your_date_column`) = 'your_formatted_date'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download