Filtering in an SQL step with datetime format column

Hi there, I am new to KNime and i am trying to implement the follwoing SQl step in KNIME DB Query reader node:
select a.col1, a.col2, a.col_dt
from from table a
where a.col_dt BETWEEN ‘20211001’ and ‘20211030’
This step works fine when I run it directly to my SQL oracle server but in KNIME I get the following message:
ERROR DB Query Reader 3:222 Execute failed: ORA-12801: error signaled in parallel query server P00M, instance ocvprxdssdbol03:pwpub003 (3)
ORA-01861: literal does not match format string
My column has a Date time format.
Can you help?

Could it be that you need to covert your date(s) first? I am not familiar with oracle syntax but there should also be some to_date or equivalent function
I assume your single quotes are only in this post different from default

I tried: cast ( a.col_dt as date) between ‘20211001’ and ‘20211030’ but I got the same error.
The synatx i am shareing works fine in SQL oracle. It does not work in KNIME.
To avoid any confusion, I need to have the filter when i am pulling the data because the table is too big.

There is also a DB Row filter node (which is excecuted in DB) you could try for filtering

Still I need to import all the records and this is not what I want. the table is too big.
Plus the next step is using a table and not a DB output

You would not import all records until you use a “not” db node in your flow
but if that does not fit your usecase then no need to try

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.