Compare dates (stored as string) in android sqlite database?

Question

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...

Answer 1

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

Answer 2

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.