SQLite select sum with monthly group by

I'm searching all over the net to find a solution to this need : I need to gather the sum of 'amout' data group by month.

What I found is this request:

Cursor cursor = mDb.rawQuery("SELECT strftime('%m', date), SUM(amount) FROM " + DATABASE_TABLE + " GROUP BY strftime('%m', date)", null);

but I'm pulling my hair of since I'm not able to gather any month from this function strftime('%m', date);

I found on the net that this could come from a date format within my DB that will not be understood by the function.

The actuel date stored within my DB is : "Thu Dec 01 00:00:00 GMT+00:00 2016"

I use this cursor to check the returned value:

Cursor cursor = mDb.rawQuery("SELECT distinct date, strftime('%m', date) FROM " + DATABASE_TABLE, null);

In the cursor (checking only the first checking of the cursor) I have:

cursor.getString(0) = Thu Dec 01 00:00:00 GMT+00:00 2016
cursor.getString(1) = null

So I correctly gather the date, it's the conversion that's not working.

Edit : As proposed and as explained, I thought the format could be misunderstood by the function (and obviously it's the case).

This is how I add date to my database: I use this : dateFormat = new SimpleDateFormat("dd/MM/yyyy");

and then this line to add the date to my Money object prior to save it into the DB : dateFormat.parse("01/12/2016")

Can you helpl me? Thanks.

Solution

Thanks to Rotwang who lead me to the format issue, my problem was located into my Database item creator

When I was creating my object, I was just putting the date as stored in my class (java.util.Date date) without formating it.

So the date was always stored with the wrong format. I had to format it during the creation phase. Hereunder the code to create my object and solving my problem.

dateFormat = new SimpleDateFormat("yyy-MM-dd"); Thanks again Rotwang

And :

public void createMoney(Money money) {
        ContentValues values = new ContentValues();

        values.put(TITLE, money.get_title());
        values.put(AMOUNT, money.get_amount());
        values.put(DETAILS, money.get_details());
        values.put(DATE, dateFormat.format(money.get_date()));
        values.put(TYPE_FK_ID, money.get_typeFkId());
        values.put(CONTACT_FK_ID, money.get_contactFkId());
        values.put(REMINDER_FK_ID, money.get_reminderFkId());

        mDb.insert(DATABASE_TABLE, null, values);
    }