CODEWITHSUNDEEP
javaandroidmysqldateandroid-contentprovider
Husky
Husky

Reputation: 1491

Getting data from MySQL database based on date

I have a table named Order consisting of 3 columns (id, date, price). I am trying to retrieve all the data where date >= startdate AND date <= endDate. The dates in the column date are saved as TEXT in this format: "MM-dd-yyyy HH:mm:ss" (10-27-2013 12:12:43)

I have tried the following SQL statements:

Cursor cursor = cr.query(PizzeriaContentProvider.CONTENT_URI_ORDER, null, PizzeriaDatabase.CELL_ORDER_DATE + " >= DATETIME ("+ startDate + ") AND " + 
                PizzeriaDatabase.CELL_ORDER_DATE + " <= DATETIME(" + endDate + ")", null, null);

Cursor cursor = cr.query(PizzeriaContentProvider.CONTENT_URI_ORDER, null, PizzeriaDatabase.CELL_ORDER_DATE + " between ( " + startDate + " AND " + endDate + ")", null, null);

But both of them throw the same exception:

java.lang.RuntimeException: Unable to start activity ComponentInfo{cz.vongrad.pizzeria/presentationLayer.ViewOrder}: 
android.database.sqlite.SQLiteException: near "14": syntax error: , 
while compiling: SELECT * FROM orders WHERE (date >= DATETIME(10-27-2013 14:01:53) AND date <= DATETIME(10-27-2013 14:01:53))

So I guess I compare the dates in a wrong format?

Upvotes: 2

Views: 674

Answers (1)

Sashi Kant
Sashi Kant

Reputation: 13465

INVERTED COMMA's are missing::

Cursor cursor = cr.query(PizzeriaContentProvider.CONTENT_URI_ORDER, null, PizzeriaDatabase.CELL_ORDER_DATE + " >= DATETIME ('"+ startDate + "') AND " + 
                PizzeriaDatabase.CELL_ORDER_DATE + " <= DATETIME('" + endDate + "')", null, null);

Upvotes: 1

Related Questions