How to set a time interval in a SQL query?

Question

Python

This is my first job with db. I have 2 tables, I want to combine them with join and set the time interval. Here is my code:

import pyodbc
import pandas as pd

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\logs\dataB.mdb;')
cursor = conn.cursor()
sql = "select report, change_date, user_, display_name from history INNER JOIN User_ ON history.user_=User_.id WHERE change_date between '2020/11/03 10:00:00' and '2020/12/04 22:00:00'"

df = pd.read_sql(sql, conn)

I want to get these dates from the user, but I can't even do that. I want to desirably drop the specific hours in the query and leave only the date. Without date and time everything works fine, but with date it gives this error:
Execution failed on sql 'select report, change_date, user_, display_name from history INNER JOIN User_ ON history.user_=User_.id WHERE change_date between '2020/11/03 10:00:00' and '2020/12/04 22:00:00'': ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')
How to solve this problem?

Answer 1

You cannot use a string in such an expression.

change_date BETWEEN  CONVERT(datetime, '2020/11/03 10:00:00') AND CONVERT(datetime, '2020/12/04 22:00:00')

The expression types must match the field type, there is no need to rely on the engine to figure it out.