Filtering in an SQL step with datetime format column

KNIME Analytics Platform
1

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?
Thanks

2

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
br

3

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.

4

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

5

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

6

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
br

7

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