Python2.7 & SQLite3: DELETE & SELECT with DATE(SUBSTR()) DO NOT working-CodePudding

Firstly, I have a table in SQLlite3 with two fields CAR (TEXT NOT NULL), checkout (TEXT NOT NULL)

car checkout
red %d%d/%m%m/%Y (for example 27/09/2021)

Second, I wrote a script which the structure is when I run it, all the entries that current date is equal or bigger than checkout to be deleted.

Third, in the same script with SELECT to check if the car is in the list and checkout is bigger than current date exclude from my available cars.

The code snippet makes the first step is the following:

try:
con = lite.connect(DB)
with con:
    paper=[]
    cur=con.cursor()
    cur.execute("DELETE FROM CHECK_TABLE WHERE DATE(substr(checkout,7,4)||substr(checkout,4,2)||substr(checkout,1,2))<=DATE(strftime('%Y%m%d',date('now')))")
    con.commit()
    print('Entries with old dates deleted.')
except lite.Error as e:
        print('Error connection: ',e)

The problem is that is not deleting anything. The strange behaviour is firstly that the SQL query works in DB Browser, Image: Proof DB Browser in Windows 10 - Python2.7 - SQLite3 the second strange behaviour is that no error is raising and the third strange is that I tested two days ago and it worked normally! I really need your thoughts.

The same logic is in the following code snippet which is the the third step that I described above with SELECT command.

def ReadDateAndCar(car):
try:
    con = lite.connect(DB)
    with con:
        paper=[]
        cur=con.cursor()
        cur.execute("SELECT DISTINCT car FROM CHECK_TABLE WHERE car='" car "' AND DATE(substr(checkout,7,4)||substr(checkout,4,2)||substr(checkout,1,2))<=DATE(strftime('%Y%m%d',date('now')))")
        free_cars=cur.fetchall()
        return free_cars
except lite.Error as e:
    print('Error connection: ',e)
    return 0

Exactly the same problems. SQL query works fine, no python error is raising, it worked few days ago. Can someone enlighten me?

CodePudding user response：

Both your queries are wrong and they don't work in DB Browser either.

What you should do is store the dates with the ISO format YYYY-MM-DD, because this is the only text date format compatible with SQLite's datetime functions like date() and strftime() and it is comparable.
If you use any other format the result of these functions is null and this is what happens in your case.

The expressions substr(checkout,7,4)||substr(checkout,4,2)||substr(checkout,1,2) and strftime('%Y%m%d',date('now')) return dates in the format YYYYMMDD and if you use them inside date() or strftime() the result is null.

Since you obtain in both sides of the inequality dates in the format YYYYMMDD then they are directly comparable and you should not use the function date().

The condition should be:

substr(checkout, -4) || substr(checkout, 4, 2) || substr(checkout, 1, 2) <= strftime('%Y%m%d', 'now')