I have a table that stores 2 seperate dates in there own columns, one is called date opened and the other is called date closed in the MySQL table.
I use PMI to insert records into the table, when I insert the record I enter a date in the date opened column.
Then when I view the table in PMI the date opened column is correct, but I also have the date “Dec 31, 1969” showing up on the date closed which is set to “NULL”.
When I check the table in MySQL the date closed column reads only “NULL”
The reason it is showing up as “Dec 31, 1969” is because the MySQL JDBC driver is converting the NULL datetime to a zero date (meaning epoch). There is an extra connection parameter you can add to the FactoryPMI datasource connection that should take care of the problem:
zeroDateTimeBehavior=convertToNull;
Make sure you have this entered in on your MySQL connection. If you do, then the MySQL JDBC driver is not respecting that setting. We can help you upgrade the MySQL JDBC driver to a newer version if you want.
In the meantime, you can get around this issue by formatting the date before it comes into FactoryPMI. That way the column can be a string instead of a datetime. Here is an example of how to format the date:
SELECT COALESCE(DATE_FORMAT(DateClosed, '%Y-%m-%d %H:%i:%s'),'') FROM Table