CODEWITHSUNDEEP
androidsqlitedate
Artenes Nogueira
Artenes Nogueira

Reputation: 1552

No results from a query with Date type in SQLite - Java/Android

I perform the following query in a SQLite database:

" ...WHERE mydate >= '" + inicialdate + "' AND mydate <= '" + finaldate + "'";

mydate is the field in the DataBase, type DATE. inicialdate and finaldate are Strings.

The idea here is to perform a query between two different dates given by the user. I use a DataPicker to allow the user input the data.
So, I get these dates returned from the DatePicker and put them in the inicialdate and finaldate Strings.

The format of the date I'm putting in the Strings is "YYYY-MM-DD".

The query returns something when I just put some value in the finaldate String, but when i put dates in the two Strings, or just in the first one, I receive nothing from the query.

Upvotes: 0

Views: 909

Answers (2)

Artenes Nogueira
Artenes Nogueira

Reputation: 1552

" ...WHERE mydate >= '" + inicialdate + "' AND mydate <= '" + finaldate + "'";

This piece of query is right. The problem was in my code, with de datapicker.

To perform a query in SQLITE I need to use the format YYYY-MM-DD. I was taking the data to perform a query directly from a datapicker, so sometimes it retrieves a data in the format YYYY-M-D. In a datapicker, when a month or day is lower than 10, it just put a single digit to represent the month or day.

Tha solution is just use an if statement to see if the number is lower than 10, then put a "0" before the number of the month or the day.

Well, this solved the problem at all

Upvotes: 0

CL.
CL.

Reputation: 180040

This works:

> sqlite3
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test(mydate date);
sqlite> insert into test values('2012-09-09');
sqlite> select * from test where mydate >= '2012-09-08' and mydate <= '2012-09-10';
2012-09-09

Does the same query work in your DB? I'd guess that the values in the table might use a different format.

Upvotes: 1

Related Questions