SQL Choosing Between Dates

Question

I run sqlite to select data between two ranges for a sales report. To select data from two dates, I use the following operator:

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

This expression captures all dates, even those that do not meet the criteria. The date format you see is in the same format that I am returning. I'm not sure what is wrong, but I acknowledge any help I can find. Thank!

Answer 1

SQLLite requires that the dates be in the format YYYY-MM-DD . Since the data in your database and the string in your query are not specified in this format, perhaps this refers to your "dates" as strings.

Answer 2

Change your data to these 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' 

Answer 3

Another way to choose 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" 

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

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

but if you want more choices, you have.

Answer 4

Or you can pass your string to a date format using the date function. Even the date is stored as TEXT in the database. Like this (the most workable option):

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

Answer 5

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

Please note that I use the format: dd/mm/yyyy

If you are using d / m / yyyy, change substr()

Hope this helps you.

Answer 6

Special thanks to Jeff and vapcguy your interactivity is truly 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')