Toad World® Forums

Toad Data Point - Passing Dates to Oracle as Parameters

Does anyone know how to make Oracle happy with TDP parameters?

CREATE_DATE > :MaxTransDate

I'm using sql variable to bind a max date being returned, but Oracle doesn't know what to do with it. It doesn't fail, but the value returned from TDB to Oracle is "1/12/2020 12:00:00 AM". Oracle won't return a result using that format and there are definitely records after that date.

Even if I try to format the input TDB converts it back to the same date format:

SELECT Cast(Max(create_date) AS DATE Format 'mm/dd/yyyy') FROM returns 1/12/2020 and TDB converts it to "1/12/2020 12:00:00 AM" which Oracle doesn't return any results for.

These don't work either.
CREATE_DATE > TRUNC(TO_DATE(:MaxTransDate,'mm/dd/YYYY'))
CREATE_DATE > TRUNC(TO_DATE(':MaxTransDate','mm/dd/YYYY'))
CREATE_DATE > TRUNC(TO_DATE('&MaxTransDate','mm/dd/YYYY'))
CREATE_DATE > TRUNC(TO_DATE (':MaxTransDate', 'YYYY-mm-dd HH24:MI:SS'))

Seems like there should be some documentation on handling data types if it's this complicated to pass a date value.