在文本数据类型之间执行的SQLite。

I made quite a silly mistake in implementing my SQLite database with a TEXT datatype for the date in the form of YYYY-mm-dd.

我在使用文本数据类型实现SQLite数据库时犯了一个非常愚蠢的错误。

Is there anyway to perform BETWEEN operations on text which is in the form of a date?

对于以日期形式存在的文本，是否存在操作之间的执行?

For example,

例如,

String sql = SELECT * FROM table WHERE column_date BETWEEN ? AND ?
selectionArgs = new String[] {"2017-01-07","2017-01-01"};
database.rawQuery(sql,selectionArgs);

I'm in an android environment.

我在一个android环境中。

Any recommendations on what I could do if I really need to get dates with BETWEEN?

如果我真的需要约会，有什么建议吗?

1 个解决方案

#1

Since you stored your dates in yyyy-mm-dd format, they should still sort correctly even as text. So, I would expect your current query to actually work as you have it right now.

由于您将日期存储为yyyyy -mm-dd格式，因此它们仍然应该作为文本进行正确的排序。所以，我希望当前查询能像现在这样工作。

The query

查询

SELECT * FROM table WHERE column_date BETWEEN '2017-01-01' AND '2017-01-07'

is identical to

是一样

SELECT * FROM table WHERE column_date >= '2017-01-01' AND column_date <= '2017-01-07'

#1