android 数据库存时间,在android sqlite数据库中比较日期（存储为字符串）？

I store dates as String in my database, in this format:

YYYY-MM-DD HH:MM:SS

which is one of supported formats (http://www.sqlite.org/lang_datefunc.html). Now I want to compare these stored dates (well, strings) with another dates. My dates are stored in column column_date, so I'm trying this:

SELECT * FROM MyTable

WHERE

(Date(column_date) >= Date (2000-01-01) AND Datetime(column_date) <= Datetime (2050-01-01))

As i read documentation, The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. so I suppose, I'm doing it right - I create date from stored string and compare it with date created from another strings.

But it doesn't work, even when column_date is date from this year, and as u can see the start and end dates are very benevolent. Tried also this (used datetime instead of date):

SELECT * FROM MyTable

WHERE

(datetime(column_date) >= Date (2000-01-01) AND datetime(column_date) <= Datetime (2050-01-01))

and this (use between instead of <= and >=)

SELECT * FROM MyTable

WHERE

(Date(column_date) between Date (2000-01-01) AND Datetime (2050-01-01))

and all other possible combinations. What the hell I'm doing wrong, am I stupid, or the documentaion lies, or I missed something very important? Trying to find solution few hours, but nothing works...

解决方案

if you are storing dates as strings in the format

YYYY-MM-DD HH:mm:ss

====> then your date field is a DateTime datatype

Thus you have to use such query

select *

from MyTable

where mydate >= Datetime('2000-01-01 00:00:00')

and mydate <= Datetime('2050-01-01 23:00:59')

you can also use the snippet given by @Joop Eggen with th between operator it's th same approche.

The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. see sqlite3 docs