I can't seem to get reliable results from the query against a sqlite database using a datetime string as a comparison as so:
select *
from table_1
where mydate >= '1/1/2009' and mydate <= '5/5/2009'
how should I handle datetime comparisons to sqlite?
update:
field mydate is a DateTime datatype
Mark Smith :
To solve this problem, I store dates as YYYYMMDD. Thus,\n where mydate >= '20090101' and mydate <= '20050505'\n\nIt just plain WORKS all the time. You may only need to write a parser to handle how users might enter their dates so you can convert them to YYYYMMDD.",
2009-12-29T18:17:19
Roger Pate :
SQLite doesn't have dedicated datetime types, but does have a few datetime functions. Follow the string representation formats (actually only formats 1-10) understood by those functions (storing the value as a string) and then you can use them, plus lexicographical comparison on the strings will match datetime comparison (as long as you don't try to compare dates to times or datetimes to times, which doesn't make a whole lot of sense anyway).\n\nDepending on which language you use, you can even get automatic conversion. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)",
2009-12-29T17:33:01