Help & DocumentationConnecting To Data Sources JDBC ConnectionsValidation 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:

2.1. Navigate to the data source editor for the affected data source

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.

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

0 Comments

Add your comment

E-Mail me when someone replies to this comment