Hi,
I am new in Knime. I started to built workflow which is using Oracle database. I used “Oracle connector” node, then I am trying to use “DB querry reader” node with built-in SQL statement. Statement is working fine, but when I add condition relating the date field then it shows me error “ORA-01861: literal does not match format string”.
Any Idea?
Simple example of SQL statement below:
Select contract.id, contract.activdate
from contract
where
contract.activdate>'2022-01-01’
Hi @AreQ , this error is likely to occur when you supply a literal date string to Oracle that is not in the format it is expecting.
I assume that the activdate column on your contract table is an oracle DATE datatype.
Oracle requires that if you pass a date as a literal without using to TO_DATE function (to state the format of the date you are supplying), that the format matches the NLS_DATE_FORMAT for your current session. Assuming you haven’t specified NLS_DATE_FORMAT, it will probably be DD-MON-YY e.g. ‘01-JAN-22’.
If you want to, you can find out what the current expected format for a date is by simply returning the current sysdate from oracle,
e.g. using SELECT SYSDATE FROM DUAL
As the date format could be modified for a given session,( using ALTER SESSION SET NLS_DATE_FORMAT = '<date format>' ), it is best with Oracle to ALWAYS wrap date literals with the TO_DATE function, so that you are making no assumptions about the current default format.
Assuming that the date literal you have is in the format YYYY-MM-DD, you should find that changing your query to the following should work:
Select contract.id, contract.activdate
from contract
where
contract.activdate>TO_DATE('2022-01-01’,'YYYY-MM-DD')
Please note that if there is a time component to your activdate column (so that the column contains a time other than midnight, and you only wish to find dates that are AFTER 2022-01-01, then you will also need to take steps to “truncate” the date, which means it will return the date with the time set to midnight.
e.g.
where TRUNC(contract.activdate)>TO_DATE('2022-01-01’,'YYYY-MM-DD')
This would have an implication on indexing though, if there is an index on contract.activdate, so if that is a problem you may have to look into ways to deal with this such as “functional indexes”.
As I mentioned, this is only a problem though if your activdate contains time as well.
For example, if contract.activdate contains the date/time ‘2022-01-01 23:32:00’, then it will be returned even though the actual date part is not greater than ‘2022-01-01’. This is because in Oracle it would be comparing ‘2022-01-01 23:32:00’ with ‘2022-01-01 00:00:00’ which you can see is “greater”.
By putting TRUNC around the date, the condition would then translate to '2022-01-01 00:00:00' > '2022-01-01 00:00:00' which then means that the row would NOT be returned, and this is likely to be what you had in mind.
Many Thanks takbb.
“TO_Date” is working fine. Such simple solution and so effective! 
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.