compare sqlite strings

I have a problem and I can't figure out whats causing it. I have a sqlite3 DB for android running. The app places some date values as text in a column. Now I want to get all entries from - lets say - this month. So normally I would do something like this:

SELECT * FORM myTable WHERE date > '2011-10-01'

However my resultset开发者_开发百科 consists of all entries. The date coloum is formatted the same way - so there is no problem. I just have no idea, what is causing this strange output.

Thank you very much for your help.

Hmm, what happens if you change that to:

SELECT * FROM myTable WHERE date like '2011-10-01%'

I have tested your problem and the results show that it actually should work like you tried. At least with a normal installation of sqlite3 that is:

sqlite> select * from myTable;
2011-09-01
2011-08-01
2011-11-01
2011-10-02
sqlite> select * from myTable where date > "2011-10-01";
2011-11-01
2011-10-02

So my guess is that you have an issue with your database format or your query does not look like the one you posted. Aside from the FORM instead of FROM which I guess is a typo.b