vendredi 17 avril 2015

How to filter data from database based on YYYY-MM using Selection and SelectionArgs[] parameter in Android SQLite Query

I am storing YYYY-MM-DD and HH:MM:SS values in two separate columns in all my SQLite tables.


I have been using the following code to filter data by supplier id and date from my SQLite database.



public double addPurchaseTotal(String supplierID, String date) {
SQLiteDatabase db = helper.getReadableDatabase();
double result = 0;
String selection = VivzHelper.COLUMN_ADD_PURCHASE_SUPPLIER_ID + " =? "
+ " AND " + VivzHelper.COLUMN_ADD_PURCHASE_DATE + " =? ";
String[] selectionArgs = {supplierID, date};
Cursor c = db.query(VivzHelper.ADD_PURCHASE_TABLE,
new String[]{"sum(" + VivzHelper.COLUMN_ADD_PURCHASE_ITEM_COST_PRICE + ")"},
selection,
selectionArgs,
null,
null,
null);
if (c.moveToFirst()) {
result = c.getDouble(0);
}
c.close();
return result;
}


The value for date parameter is obtained from a date picker. As mentioned earlier, date value under the VivzHelper.COLUMN_ADD_PURCHASE_DATE is stored in YYYY-MM-DD format. I would like to filter my data based on YYYY-MM (year and month alone). How can this be done?


Aucun commentaire:

Enregistrer un commentaire