SQLite DateTime comparison

Question

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

Solution:

following the datetime function and having a string format as YYYY-MM-DD HH:mm:ss i achieved good results as follows

select * 
  from table_1 
  where mydate >= Datetime('2009-11-13 00:00:00') 
  and mydate <= Datetime('2009-11-15 00:00:00')

Answer 1

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

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

Answer 2

To solve this problem, I store dates as YYYYMMDD. Thus, where mydate >= '20090101' and mydate <= '20050505'

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

Answer 3

You could also write up your own user functions to handle dates in the format you choose. SQLite has a fairly simple method for writing your own user functions. For example, I wrote a few to add time durations together.