How do I SELECT data between two dates with the dates as a variable

Question

Using Sqlite3 and Python, I want to able to get data between two dates with the dates as a variable. For example

date1 = 2020-10-16
date2 = 2020-10-18

"SELECT SUM (total) FROM table WHERE date BETWEEN '%"+date1+"%' AND '%"+date2+"%'"

How can I have BETWEEN grab the date in the variables. When I run this and print the query, the sum is 0 even though I place values inside the rows in (total)

2020-10-16=1994 This is probably not what you want to store in date1. Rather make a string out of it. — flabons, Oct 17 '20 at 19:42
@Community the duplicate link that you propose has nothing to do with this question. — forpas, Oct 17 '20 at 20:11

score 2 · Answer 1 · answered Oct 17 '20 at 19:44

Your variables should be strings:

date1 = "2020-10-16"
date2 = "2020-10-18"

because a variable like:

date1 = 2020-10-16

is evaluated as 1994 = 2020 - 10 -16.

You should use ? placeholders in your query for the 2 date parameters:

query = "SELECT SUM(total) AS total FROM tablename WHERE date BETWEEN ? AND ?"

and pass the parameters when you execute the query:

curs.execute(query, (date1, date2))

How do I SELECT data between two dates with the dates as a variable

1 Answers1