SQL statement doesnt return value between two dates

Question

I'm trying to select a specific date between two dates. The forma its stored in the DB is M/d/yyyy. What I'm trying to do, is passing two dates, and I want to select all data of a column between this two dates. I know there is data in the DB, but it doesnt bring anything for some dates.For some dates, it brings the data, but for some dates it brings the data.

Example:

When I choose 7/10/2011 as first date, and as second date 7/16/2011, it brings the data. But When I choose 7/9/2011 as first date and as second date 7/16/2011, it doesnt bring anything.

Here is my query:

Cursor cursor = db.query(CONTACT_DATA, new String[] { "duration" },
                "date >= ? AND date <= ?",
                new String[] { firstDate, secondDate }, null, null, null);

If anyone can help with this question. Thanks!

See if this helps: http://stackoverflow.com/questions/3838527/android-java-date-difference-in-days — nickfox, Jul 18 '11 at 03:18
wow, I try to post an answer and it posts as a comment. I did it 3 times. — nickfox, Jul 18 '11 at 03:19

Doug Currie · Answer 1 · 2011-07-18T03:33:00.170

0

SQLite does not have a native date type; it is comparing your dates as strings. That's why when the dates are in the same month and year, and have the same number of digits in the day, the query appears to work, but that is just luck.

Typically SQLite users format their date strings using ISO-8601 format (YYYY-MM-DD) which sorts correctly as strings.

SQLite does have date functions -- if your dates are all well formed you may be able to use these, along with ltrim and rtrim to convert your dates to a format usable in queries.

edited Jul 18 '11 at 03:33

answered Jul 18 '11 at 03:27

Doug Currie

40,708
1
95
119

So the correct forma I should use to query between the two dates would be this: YYYY-MM-DD? Thanks. – rogcg Jul 18 '11 at 11:24
Yes, as long as that is also the format of the data in the column you are comparing against. – Doug Currie Jul 18 '11 at 17:27

score 0 · Accepted Answer · answered Aug 02 '11 at 12:54

0

I'm gonna store the dates as miliseconds(epoch format), so its easier to compare. And when printing the date, I'll convert it.

answered Aug 02 '11 at 12:54

rogcg

10,451
20
91
133

SQL statement doesnt return value between two dates

2 Answers2