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.