[sql] SQL Select between dates

I am running sqlite to select data between two ranges for a sales report. To select the data from between two dates I use the following statement:

SELECT * FROM test WHERE date BETWEEN "11/1/2011" AND "11/8/2011";

This statement grabs all the dates even those outside the criteria. The date format you see entered is in the same format that I get back. I'm not sure what's wrong but appreciate any help I can find. Thanks!

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.

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

Hope this will help you.

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-8-11')

Special thanks to Jeff and vapcguy your interactivity is really encouraging.

Here is a more complex statement that is useful when the length between '/' is unknown::

SELECT * FROM tableName
WHERE julianday(
    substr(substr(date, instr(date, '/')+1), instr(substr(date, instr(date, '/')+1), '/')+1)
||'-'||
    case when length(
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1),'/')-1)
    )=2
    then
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    else
    '0'||substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    end
||'-'||
    case when length(substr(date,1, instr(date, '/')-1 )) =2
    then substr(date,1, instr(date, '/')-1 )
    else
    '0'||substr(date,1, instr(date, '/')-1 )
    end
) BETWEEN julianday('2015-03-14') AND julianday('2015-03-16') 

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'