Help & DocumentationSystem AdministrationManaging the Metric Insights EnvironmentMonitoring Your SystemValidation errors: "Value '0000-00-00' can not be represented as java.sql.Date"

Validation errors: "Value '0000-00-00' can not be represented as java.sql.Date"

ANALYSIS:

This is a MySQL-specific problem that does not happen often.  It is caused by storing zero dates ('0000-00-00 00:00:00') in MySQL and trying to convert those into date objects in Java. Unfortunately, Java does not understand dates in this format, so the MySQL JDBC driver will throw this error by default.

RECOMMENDED ACTION:

Apart from modifying the source data, the easiest solution is to modify the JDBC connection URL for the affected Data Source to include ?zeroDateTimeBehavior=convertToNull as shown below.

REFERENCES:

For more information about this JDBC parameter, see the Datetimes bullet under section 20.3.3.3.3 of the MySQL Manual.

1. Problem examples:

1.1. Example: "Value '0000-00-00' can not be represented as java.sql.Date"

Example: "Value '0000-00-00' can not be represented as java.sql.Date"

1.2. Example: "Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP"

Example: "Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP"

2. Solution:

Make sure the data source drop down is pointing to the appropriate data source, then click the gear icon to the right to edit the data source.

Navigate to the data source editor for the affected data source

2.2. Fix the SQL Data Source URL

Fix the SQL Data Source URL

The suggested fix is to append '?zeroDateTimeBehavior=convertToNull' to the end of the JDBC string.

So the original string 

jdbc:mysql://localhost:3306/demo

would become 

jdbc:mysql://localhost:3306/demo?zeroDateTimeBehavior=convertToNull