Trying to get python sqlite to compare against a DATE and not the actual DATETIME field

I have this bit of code that is not working…

currentTime = dt.utcnow().date() get just the day has to be UTC cause that what stored in db.
print(currentTime)  #shows day of 2020-09-03
cur = conn.cursor()
cur.execute("SELECT f.user_id, u.first_name, f.location, f.checkin, f.checkout FROM auth_user u JOIN covidlog_onsitelog as f ON (f.user_id=u.id) WHERE CAST(f.checkin as 'DATE()')  = ? or CAST(f.checkout as 'DATE()') =?", (currentTime,currentTime))

The field was created as a model in django and has in it items like:

2020-09-03 00:40:58.901602

But I just want to compare the DAY regardless of the time…though it is not working it seems.

So to me this looks right but does not yield the results so I can only assume it is still somehow comparing the date with the time stamp too… any idea how NOT to do this and literally compare against the date?

Answer

Check if your f.checkin and f checkout fields are datetimestring in which case you may use date(f.checkin) and date(f.checkout) instead of CAST(f.checkin As ‘Date()’) You may want to have a look at “https://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date” and to: “https://www.tutlane.com/tutorial/sqlite/sqlite-date-function#:~:text=Syntax%20of%20SQLite%20date(),datetime%20string%20value%20into%20date.&text=The%20SQLite%20date()%20function,YYYY%2DMM%2DDD%20format.” Your WHERE clause will look like:

WHERE
    DATE(f.checkin) = ? OR DATE(f.checkout) = ?