I encounter problems when I try to write data to a MSSQL DB. After reading a CSV file I convert a string column wit a String To Date/Time node what produces values like 2017-05-22T18:30:00.0
The Database Writer node then correctly recognizes SQL Type timestamp. But every attempt to write this data into the DB converts this value to 2017-05-22 without the rest of the time. The problem seems to be KNIME because I have a unique constraint using the date field and KNIME logs: Error while adding row #9 (Row7), reason: Cannot insert duplicate key row in object 'dbo.xyz' with unique index 'IX_xyz_UNIQUE'. The duplicate key value is (c1, c2, c3, 2017-05-22)
My KNIME version is 3.3.0
Shame on me. Reading my own post again, I recognized my own argumentation about the limitation to KNIME as the root cause was erroneous. Then I asked the DBA what column type is actually used and it was only Date instead of Datetime. Sorry!
just to add here: we are heavily working on a new Date&Time Integration, which will be published soon. We know the current integration has some limitations, which will be covered with the new integrations.
I am using alteryx and the any kind of data type conversion work well for database or flat file etc
and very easy to change data type with table column in configuration window.
I suggest you to look at altryx product as reference how they can do well.
Please have a look at the screenshot as reference how alteryx developed well and KNIME can develop something similar with Alteryx configuration then many people use KNIME products.
Thanks, I'd be interested in that too. I have a lot of time series data to store in Redshift / PostGres