TO_DATE and Database Row Filter

KNIME Analytics Platform
1

New KNIME user here, so forgive the basic question.

I’m trying to filter based on a date, however the only way I get get this to work is to use the TO_DATE function in the value field, when ideally I would just like to use the date itself. The column I want to filter on has the “Date and Time” type.

For example, this is what the sql looks like that works:

SELECT * FROM (SELECT * FROM TABLE1) table_424443619 WHERE “EFF_DT”>= TO_DATE(‘2018-10-31’, ‘YYYY-MM-DD’)

whereas I would have hoped I could have just used this:

SELECT * FROM (SELECT * FROM TABLE1) table_424443619 WHERE “EFF_DT”>= 2018-10-31

However this yields a “Could not fetch data from database, reason: ORA-00932: inconsistent datatypes: expected DATE got NUMBER” error.

Am I doing something wrong?

2

Hi Matbow,

The current Database Row filter doesn’t distinguish different types and considers the input as a string, that’s why you have to manually convert the string to a correct type.
The new database framework implements the type mapping and if you like you could test it with DB nodes (in Labs) from December 6th.

Hope it helps.
Daria

1 Like