How to filter data using to_date function to filter YYYYMM from timestamp in sql query from python

Question

I am getting an error while trying to filter data based on year month from timestamp column.

INPUT

 domain_name   INSERT_DATE        LOC
KNEU/TEREX   2019-05-08 12:05:13   AL
KNEU/TEREX   2019-05-16 11:45:49   BI
KNEU/TEREX   2019-05-21 11:45:49   MY
KNEU/TEREX   2014-09-11 11:45:49   SG
KNEU/TEREX   2014-09-11 11:45:49   IL

MY code

sql3 = """select * from location where domain_name = 'KNEU/TEREX'and to_date(INSERT_DATE,'YYYY-MM')=2019-05 """
dfotm4 = pd.read_sql_query(sql3, connection3)

Error

DatabaseError: Execution failed on sql 'select * from location where domain_name = 'KNEU/TEREX'and to_date(INSERT_DATE,'YYYY-MM')=2019-05 ': ORA-00932: inconsistent datatypes: expected DATE got NUMBER

EXPECTED OUTPUT

domain_name   INSERT_DATE          LOC
KNEU/TEREX   2019-05-08 12:05:13   AL
KNEU/TEREX   2019-05-16 11:45:49   BI
KNEU/TEREX   2019-05-21 11:45:49   MY

What is wrong in my code.

Answer 1

In order to compare year and month in SQL, we can make use of the MONTH(expression) and YEAR(expression)

SELECT * FROM location WHERE domain_name = 'KNEU/TEREX' AND YEAR(INSERT_DATE) = 2019 AND MONTH(INSERT_DATE) = 5;

Error in your code is because you haven't wrapped 2019-05 in quotes, it should've been '2019-05'