How to retrive all the data from sqlite according to the date

Question

I am Facing a problem in a query.In which i retrive the data from database according to the date like if todays date is 17/09/2016 i want all the data which is saved on this date the problem is when i retrive its show me only one data and there are many data present in my database i cant get out from this problem. i know that it is a silly question but i am stuck in that from 2 to 3 days.

 sqLiteDatabase.rawQuery("SELECT * from mytable where Date = (select max(Date) from mytable WHERE Date < DATE('now') );", null);

   Cursor c = sqLiteDatabase.rawQuery("SELECT * from BluetoothDevice WHERE Date = date('now');", null);

    while (c.moveToNext()) {
        Date date = new Date();
        date.setTime(Long.valueOf(c.getString(c.getColumnIndex("Date"))));
        result.add(
                new BluetoothDevice(
                        c.getString(c.getColumnIndex("Device")),
                        c.getString(c.getColumnIndex("Address")),
                        date

                )
        );
    }
    c.close();
    return result;
}

Show the code that reads the rows. – CL. Sep 16 '16 at 18:47 — CL., Sep 16 '16 at 18:47
Save a date always as long! – XxGoliathusxX Sep 16 '16 at 18:56 — XxGoliathusxX, Sep 16 '16 at 18:56

score 0 · Answer 1 · answered Sep 16 '16 at 18:45

0

That query would never retrieve TODAY's data. date('now') will always be now, so Sep 16, 2016. You then query for anything BEFORE (<) that day, so you only get records Sep 15, 2016 and earlier. You then take the MAX of those earlier dates, which can never be HIGHER than Sep 15 (because that's what your where specifies) and use an equality test on that.

So effectively, for a query on Sep 16th, you're running where date = 'Sep 15'

Why can you just have

SELECT ... WHERE date = date('now')

?

answered Sep 16 '16 at 18:45

Marc B

356,200
43
426
500

1

how are you storing your dates? as actual date/datetime fields, or as strings? if they're strings, you HAVE to ensure that they're in the same format that `date('now')` erturns. `'sep 16, 2016' == '2016-09-16'` are FALSE by string comparison standards, but by human standards represent the same day. – Marc B Sep 16 '16 at 18:50
i have datetime – Badprince Sep 16 '16 at 18:51
then you have to account for the time component as well. `'2016-09-16 12:52:00' = '2016-09-16'` is FALSE, because the date-only value is assumed to have an invisible `00:00:00` time component. – Marc B Sep 16 '16 at 18:52
can u give me the exact query bcuz i am new to android – Badprince Sep 16 '16 at 18:53
http://stackoverflow.com/questions/3014667/how-can-i-convert-datetime-to-date-format-in-sqlite – Marc B Sep 16 '16 at 18:55
but this will return only date ?' – Badprince Sep 16 '16 at 18:58
Following your current format, you could have the query like so: "SELECT * from mytable where Date LIKE %('now')%". Of course, if the date formatting is different (e.g. yy/mm/dd vs mm/dd/yy) this still won't have results – Demonsoul Sep 16 '16 at 20:32
I have yyyy/MMMM/dd – Badprince Sep 17 '16 at 16:52

How to retrive all the data from sqlite according to the date

1 Answers1