Project and Portfolio Management Practitioners Forum
cancel

Comparision between custom date field and SYSDATE

SOLVED
Go to solution
Highlighted
Raj Ghimire
Outstanding Contributor.

Comparision between custom date field and SYSDATE

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.

1. trunc(TO_DATE(KRD.PARAMETER3, 'yyyy-mm-dd hh24:mi:ss')) <= trunc(SYSDATE)
or,
2. trunc(to_date(to_char(to_date(KRD.PARAMETER3,'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'))<= trunc(SYSDATE)
or,
3. trunc(to_date(to_char(KRD.PARAMETER3,'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'))<=trunc(sysdate)

Can anyone give me any idea on this ?

Thanks in advance.
-Raj
2 REPLIES
Erik Cole_1
Outstanding Contributor.
Solution

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)
Raj Ghimire
Outstanding Contributor.

Re: Comparision between custom date field and SYSDATE

Eric, you Rock !!
Worked perfectly.
Thanks a million !
-Raj