Hello Everyone ! I am trying to write a data source with a query where it is supposed to display output according to the condition "custom_date_field_value<=sysdate" but somehow running into oracle errors: "ORA-01861: literal does not match format string"
When I query the field value for custom field with SELECT it gives me value like this: 2009-06-01 17:27:01.
So, I have tried numerous ways like below but everytime get the same "ORA-01861: literal does not match format string" error.
Re: Comparision between custom date field and SYSDATE
This is probably because as the query is executing, it is running across some data for another request type that is stored in the same PARAMETER3 column but is not a date.
Keep in mind that Oracle does not always apply your query filters in the same order that you place them in the query, so it may be trying to apply this date filter before it even filters for a particular request type. It just depends on how the optimizer feels at that time!
I usually get around this by using an inline view, like
Select * from (select * from KCRT_REQUEST_DETAILS where REQUEST_TYPE_ID = 30216) where trunc(to_date(PARAMETER3, 'yyyy-mm-dd hh24:mi:ss')) >= trunc(sysdate)