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

android, date, sqlite

Solution

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

Problem

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

Original source