Search code examples
javaandroidsqlsqliteandroid-sqlite

Select sqlite android

I have a query with a condition but it is returning an empty result even though I have the data that matches these conditions. I am a beginner, so I'm not sure if this query is correct.

public Cursor raw() {    
    SQLiteDatabase db = this.getReadableDatabase();
    String select = "SELECT * FROM table_person WHERE data >= " + SelecionarPeriodo.dataInicial + " AND " + "data <=" + SelecionarPeriodo.dataFinal;

    Cursor res = db.rawQuery( select, new String[]{});
    Log.i("String", select);

    return res;
}

enter image description here

If I remove this condition and use a query as follows

SELECT * FROM table_person

I have the results and the columns corresponding to the dates I'm trying to get.

Solution

  • The way you have written your query you are actually comparing the column data with 0, because a value like 14/12/2020 is considered as an expression containing divisions between the numbers 14, 12 and 2020 which has 0 as result.

    If you want to compare dates in SQLite you must use a comparable format like YYYY-MM-DD, which is the only valid text date format for SQLite.
    So if you stored the dates in the column data in any other format you'd better change it to YYYY-MM-DD.
    You must also use the same format for the parameters SelecionarPeriodo.dataInicial and SelecionarPeriodo.dataFinal which will be compared to data.

    Then use the recommended and safe way to pass the parameters in the 2nd argument of rawQuery():

    public Cursor raw() {    
    SQLiteDatabase db = this.getReadableDatabase();
    String select = "SELECT * FROM table_person WHERE data >= ? AND data <= ?" + ;
    Cursor res = db.rawQuery(select, new String[]{SelecionarPeriodo.dataInicial, SelecionarPeriodo.dataFinal});
    Log.i("String", select);
    return res;
}