SQLite DateTime comparison

Question

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.

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

I had the same issue recently, and I solved it like this:

SELECT * FROM table WHERE 
    strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

The following is working fine for me using SQLite:

SELECT * 
    FROM ingresosgastos 
    WHERE fecharegistro BETWEEN "2010-01-01" AND "2013-01-01"

Following worked for me.

SELECT *
FROM table_log
WHERE DATE(start_time) <= '2017-01-09' AND DATE(start_time) >= '2016-12-21'