0

i using sqlite datebase i want to select some rows of specific date. where i am using e_date as text

CREATE TABLE expenses ( id integer PRIMARY KEY AUTOINCREMENT, e_type text NOT NULL, e_date text NOT NULL, e_amt text NOT NULL, e_method text NOT NULL, desc text );

where i am trying to select using query

select * from expenses where e_date between 31/7/2014 and 15/8/2014;

but its not working help me out..

Om R Kattimani
  • 105
  • 1
  • 9

1 Answers1

2

Try storing your dates in an ISO 8601 format such as yyyy-MM-dd. Examples for your two dates...

2014-07-31
2014-08-15

Using that format means you can actually sort alphanumerically and also compare them. If you then change your query to something like the following, it will work...

SELECT * from expenses WHERE e_date >= '2014-07-31' AND e_date <= '2014-08-15'

EDIT: Thinking about it, if you use the ISO 8601 format you can actually use the SQL BETWEEN operator but you must use single quotes to surround the strings.

SELECT * from expenses WHERE e_date BETWEEN '2014-07-31' AND '2014-08-15'

Also, just a tip - don't use id for your first column, use _id instead (notice the preceding 'underscore'). Having a column called _id is necessary if you use various Android Adapters and AdapterViews.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
Squonk
  • 48,735
  • 19
  • 103
  • 135
  • Good suggestion on renaming `id`. But I suggest using **a trailing underscore**, `id_`, rather than leading. The SQL spec explicitly promises to never have reserved words with a trailing underscore. Therefore using a trailing underscore on all your SQL names means never having to worry about, or suffer from, collisions with reserved words and keywords. See [my answer](http://stackoverflow.com/a/19758863/642706) for details. – Basil Bourque Aug 17 '14 at 06:33
  • @BasilBourque : As I mentioned - when using Android Adapter and AdapterView classes the dataset returned by a query when used with a a Cursor *MUST* have a unique column called `_id`. There's even a specific exception which is thrown if it doesn't have a column called `_id`. – Squonk Aug 17 '14 at 09:10