在androidsqlite数据库中比较日期(存储为字符串)?-Comparedates(storedasstring)inandroidsqlitedatabase?

17  

if you are storing dates as strings in the format

如果将日期存储为格式中的字符串

YYYY-MM-DD HH:mm:ss  

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

==== =>那么您的日期字段就是一个DateTime数据类型

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.

您也可以使用@Joop Eggen和th之间的操作符，它是相同的方法。

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

BETWEEN运算符在逻辑上等同于一对比较。“x在y和z之间”等价于“x>=y和x<=z”，但x表达式只计算一次。看到sqlite3文档

6  

SELECT *
FROM MyTable
WHERE 
    column_date BETWEEN '2000-01-01 00:00:00' AND '2050-01-01 23:00:59'

Should do the trick. This only uses strings but you said that would be fitting.

应该足够了。它只使用字符串，但你说它是合适的。

The error was the missing time part or not doing a substr on the date part only. And then data/time conversion functions might give wrong things too.

错误是丢失的时间部分或仅在日期部分不执行子str。然后数据/时间转换函数可能也会出错。

3  

I think your issue is here...

我认为你的问题在这里……

AND Datetime(column_date) <= Datetime (2050-01-01))

Why are you using Datetime here instead of Date?

为什么要在这里使用Datetime而不是日期?

Try this:

试试这个:

SELECT  * FROM MyTable WHERE (Date(column_date) >= Date (2000-01-01) AND Date(column_date) <= Date (2050-01-01))

2  

Date filed in the SQLite table will be TEXT and date data should be stored as "2012-12-31 00:12:00" format that comparison dose not give you trouble and if we consider that fields are date_from and date_to and we are storing current date at to_date then we should get the current date as bellow and

SQLite表中的日期提交日期将文本和数据应该存储为“2012-12-31 00:12:00”格式,比较不给你麻烦,如果我们考虑到字段date_from date_to和我们存储当前日期to_date然后我们应该得到当前日期详见下表

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String to_date = sdf.format(new Date());

and the SQL query should be

SQL查询应该是

    crs = db.rawQuery(
      "SELECT _id, date_from, date_to, done_or_not, list_name " +
      "FROM list_tbl " +
      "WHERE " +
      "date_from <= Datetime('"+to_date+"') AND date_to >= Datetime('"+to_date+"')", null);

0  

Compare dates (stored as string) in android SQLite database. Use date in 12/07/2015 format and use a query like this:

比较android SQLite数据库中的日期(以字符串形式存储)。使用日期为12/07/2015，并使用如下查询:

SELECT * FROM tableName WHERE Date >=('12/07/2015') and appointmentDate <('13/07/2015')";

This works for me.

这适合我。