CODEWITHSUNDEEP
androidsqlite
Rodrigo
Rodrigo

Reputation: 153

SQLite search by month

I ned to search the records based on a month but i have no success for now, with the complete date it's ok, but searching by month it's not. Here is my helper:

    public String getSingleTransacaoSum(String date) {

    SQLiteDatabase db = this.getReadableDatabase();
    double sum = 0;

    //Cursor cursorf = db.rawQuery("SELECT * FROM " + TABLE_TRANSACOES
        //  + " WHERE data = ? ", new String[] { date });

    Cursor cursor = db.rawQuery("SELECT * FROM "+TABLE_TRANSACOES+" WHERE strftime('%m', ' DATETIME') = ?",new String[] { date });

    while (cursor.moveToNext()) {
        sum += cursor.getDouble(cursor.getColumnIndex("valor"));
    }
    cursor.close();
    return String.valueOf(sum);
}

Activity:

    btGetSum.setOnClickListener(new OnClickListener() {
        @Override
        public void onClick(View view) {
            try {
                dia = (String) spinDia.getSelectedItem();
                mes = (String) spinMes.getSelectedItem();
                ano = (String) spinAno.getSelectedItem();
                String dataSendTo = dia + "/" + mes + "/" + ano;

                dbhelper.getSingleTransacaoSum(dataSendTo);

                tvValor.setText("R$ "
                        + dbhelper.getSingleTransacaoSum(dataSendTo));

            } catch (Exception erro) {
                mensagemExibir("Erro Ao Buscar", "" + erro.getMessage());
            }

        }
    });

If anyone can help, thanks.

Upvotes: 0

Views: 152

Answers (2)

josepn
josepn

Reputation: 367

DATETIME is a bad name for a column, it is the name of a function. You have to remove the column name in apostrophes strftime (...):

SELECT * FROM TABLE_TRANSACOES WHERE STRFTIME('%m', column_with_datetime) = ?

Upvotes: 1

Sean C.
Sean C.

Reputation: 228

This should be able to be done usinga LIKE clause, IE the following

SELECT * FROM TABLE_TRANSACOES
WHERE DATETIME LIKE '%<month>%'

or in java,

db.rawQuery("SELECT * FROM "+TABLE_TRANSACOES+" WHERE DATETIME LIKE '%/"+monthYouWantToSearchFor+"/%'");

I'm assuming that DATETIME is the column you want to query.

Upvotes: 0

Related Questions