How do I set the NLS_DATE_FORMAT for the Database Reader node in Knime?

[saqib]

I have an Oracle query that requires the NLS_DATE_FORMAT for the session to altered. How do I set the NLS_DATE_FORMAT for the Database Reader node in Knime?

Here is the query:
SELECT trunc(estimatedenddate, ‘hh’) AS REG_DATE, count(*)
FROM (select attr.attr_value, TO_DATE((DATE ‘1970-01-01’ + ( 1 / 24 / 60 / 60 / 1000) * attr.attr_value), ‘yyyy-mm-dd HH24:mi:ss’) AS estimatedenddate
from attr WHERE attr_name=‘createTimestamp’) GROUP BY trunc(estimatedenddate, ‘hh’)
ORDER BY REG_DATE DESC

You can try out the query using the following dbfiddle:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=cb823294ee7c3b87f121b36dfeea5a01

[cpadilla]

Hello @saqib,

Could you elaborate on what you are trying to achieve?
You may be able to make this work by using a set of DB nodes to connect, then execute the Alter statement and then the Select statement.

Regards,
Cynthia

[ipazin]

Hi @saqib!

If you want to do it in Database Reader node your alter statement should come before you select statement. I have done the same thing in Database Table Selector and it worked. Shouldn’t be different for Database Reader node. Try it out and if you encounter any problem get back to us please.

Here is picture:

Br,
Ivan