strftime function doesn't retrieve any records from sql database in android

Question

I am creating an Expense table with 3 fields category(Text), amount(Integer) and date(Date).

    String CREATE_EXPENSE_TABLE = "CREATE TABLE " + Constants.EXPENSE_TABLE_NAME + "("
            + Constants.ID_KEY + " INTEGER PRIMARY KEY," + Constants.EXPENSE_CATEGORY + " TEXT,"
            + Constants.EXPENSE_AMOUNT + " INTEGER," + Constants.EXPENSE_DATE + " DATE" +  ")";
    db.execSQL(CREATE_EXPENSE_TABLE);

I am getting the date from user using a DatePicker and saving it to the database as follows:

    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(Constants.EXPENSE_DATE, expense.getDate().getTimeInMillis());

I want to retrieve expenses of a particular month and for that I use strf()

Cursor cursor = db.rawQuery("select amount from Expense where strftime('%m', date) = '7'", null);

However no records are returned.

I tried the same with strf() - year parameter

 Cursor cursor = db.rawQuery("select amount from Expense where strftime('%Y', date) = '2017'", null);

Again no records.

What am I doing wrong here. Kindly guide me.

Answer 1

You need to store the date value as string format. Below are the valid formats for storing a date in the database.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD