Search code examples
androidsqliteandroid-sqlite

SQLite Select Query not getting all entries

I have 3 entries added in my database, one of the fields contain a date saved as a string, when i query with "SELECT * FROM 'TABLE' ", i get all 3 of them, then when i print their corresponding date i get, in this example

2019-5-4 14:20:00

2019-6-4 14:20:00

2019-7-4 14:20:00

But when i try to use "SELECT * FROM 'TABLE' WHERE DATE BETWEEN '2019-4-1' AND '2019-7-10'" the query contains first two results, but not the last one. It will contain the 3rd one when i change the month from 7 to 8, or when the day in second date is changed from double digits to one digit, so everything from 4 to 9, instead of 2019-7-10 there will be 2019-7-9.

How can I get all the results using double digits as days in second date?

Solution

  • If you want to compare dates ignoring timestamps, then you have to do it explicitly: SELECT * FROM 'TABLE' WHERE DATE(DATE) BETWEEN '2019-04-01' AND '2019-07-10'"