pandasdateparametersformatread-sql

Pandas read_sql_query - date params

I am trying to add two date parameters to a SQL query in Pandas. Following example query:

trades = """
Select
trade_date,
units
from
transactions
where
trade_date BETWEEN :startdate and :enddate
"""

I am now defining the dates in MM/DD/YYYY format (as they are stored like that in the database)

today = pd.Timestamp.today().strftime('%-m/%-d/%Y')
yday = date.today() - timedelta(days=1)
yday = yday.strftime('%-m/%-d/%Y')

trades_df = pd.read_sql_query(trades, con, params = {"startdate":today, "enddate":yday})

But when passing the parameters like above I am getting error message ORA-01843: not a valid month

What is wrong here?

Thanks much

Solution

  • You mentioned about trying to change to %m/%d/%Y, which still failed.

    We can try to first change it DD/MM/YYYY and then cast TO_DATE in select query

    today = pd.Timestamp.today().strftime('%m/%d/%Y')
yday = (date.today() - timedelta(days=1)).strftime('%m/%d/%Y')

    SELECT query

    trades = """ 
SELECT trade_date, 
units FROM transactions 
WHERE TO_DATE(trade_date, 'MM/DD/YYYY') -- try with or without casting in trade_date
BETWEEN TO_DATE(:startdate, 'MM/DD/YYYY') AND TO_DATE(:enddate, 
'MM/DD/YYYY') 
"""

    An example in Fiddle, seems to work OK