Delete a record from SQLite database WHERE datetime contains date from variable

Question

I want to DELETE every record with a datetime (e.g. 2022-11-10T??????????) using a date (e.g. 2022-11-10) from a variable. For example:

last_Date = datetime.strftime(datetime.now() - timedelta(1), '%Y-%m-%d')

And use SQL like this:

cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE datetime = (?)
""", (last_Date))

Answer 1

The value of the variable last_Date is a formatted as YYYY-MM-DD date string but the column datetime in the table contains timestamps in the format YYYY-MM-DDT?????????? so you can't compare them with the = operator.

Use SQLite's strftime() or date() function to format the datetimes also to YYYY-MM-DD:

cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE strftime('%Y-%m-%d', datetime) = ?
""", (last_Date,))

or:

cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE date(datetime) = ?
""", (last_Date,))

If what you actually want is delete all yesterday's rows you can do it without passing a parameter, by using only SQL code:

cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE date(datetime) = date('now', '-1 day'))
""")