Getting incorrect datetime from Oracle Query

KNIME Analytics Platform
1

Hello All,

I am using the following SQL in the Database Reader node with Oracle:
SELECT
TO_DATE(SYSDATE, ‘yyyy-mm-dd HH24:mi:ss’) “NOW”
FROM DUAL

The result contains the correct Date but the time is always 00:00:00.

This query works in Oracle Workbench. I am not sure why it is not working in Knime

Any ideas?

2

Maybe Workbench is wrong? Oracle’s LiveSQL ( https://livesql.oracle.com/ ) returns a TO_DATE as indeed a date without a calculated time of day attached (as the docs seem to imply https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm ), using TO_CHAR returns a string representing the format specified.

22%20PM
22%20PM650×539 24.9 KB
08%20PM
08%20PM678×533 25.8 KB

3

@quaeler, you have to set the NLS_DATE_FORMAT with LiveSQL. For e.g. see:

57%20PM

4

Ah - good to know. Sorry for the red herring.