MySQL Writing DateTime Bug

There is a bug writing a DateTime value to a MySQL database when the DateTime is converted from a string in the format "yyyy-MM-dd HH:mm:ss" (that is, without any milliseconds).

The attached show two failing examples and two successful examples.

Case #1: StringDateTime = 1999-12-31 23:59:59

If using the "String to Date/Time" node to directly generate a DateTime value with the format "yyyy-MM-dd HH:mm:ss" then the MySQL Database Writer will fail with the error:

Error while adding row #2 (Row0), reason: 
Data truncation: Incorrect datetime value: '23:59:59' 
for column 'ConvertedDateTime' at row 1

But if you first add a ".000" to the end of the string and use the "String to Date/Time" node with the format "yyyy-MM-dd HH:mm:ss.S" (to catch the zero milliseconds) then MySQL will correctly write the data.

Case #2: StringDateTime = 2001-01-01 01:01:01

This is the more serious bug as the data will be written but will be bad data.

Directly converting the string "2001-01-01 01:01:01" to a DateTime will result in an apparently successful database write. But querying the data will return the result "1/1/2001 12:00:00 AM" (the time has been ignored).

But first appending the zero milliseconds ".001" string to the end as above will cause the data to be correctly written.

While it would be obvious to blame the MySQL driver (mine was up-to-date as of a month ago) I suspect this is something that should have been handled withing KNIME.


KNIME had a problem with date and time cells without milliseconds. This problem has been fixed with KNIME 3.1.2. Can you please update KNIME and run your tests again.