SQL Select between dates

Solution 1:

SQLLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

Solution 2:

Change your data to that formats to use sqlite datetime formats.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SELECT * FROM test WHERE date BETWEEN '2011-01-11' AND '2011-08-11'

Solution 3:

One more way to select between dates in SQLite is to use the powerful strftime function:

SELECT * FROM test WHERE strftime('%Y-%m-%d', date) BETWEEN "11-01-2011" AND "11-08-2011"

These are equivalent according to https://sqlite.org/lang_datefunc.html:

date(...)

strftime('%Y-%m-%d', ...)

but if you want more choice, you have it.

Solution 4:

SELECT *
FROM TableName
WHERE julianday(substr(date,7)||'-'||substr(date,4,2)||'-'||substr(date,1,2)) BETWEEN julianday('2011-01-11') AND julianday('2011-08-11')

Note that I use the format: dd/mm/yyyy. If you use d/m/yyyy, Change in substr().

Solution 5:

Or you can cast your string to Date format with date function. Even the date is stored as TEXT in the DB. Like this (the most workable variant):

SELECT * FROM test WHERE date(date) 
BETWEEN date('2011-01-11') AND date('2011-08-11')