How to Handle '0000-00-00 00:00:00' DATETIME Values in JDBC?

Handling DATETIME Values '0000-00-00 00:00:00' in JDBC

The problematic '0000-00-00 00:00:00' DATETIME value, representing a quasi-null state, often triggers an exception when attempting to retrieve it as a string using ResultSet.getString(). To resolve this, consider the following options:

1. Casting to CHAR

To obtain the raw DATETIME string as it appears in the database, employ a MySQL-specific query:

SELECT CAST(add_date AS CHAR) AS add_date

2. JDBC URL Configuration

Alternatively, you can append zeroDateTimeBehavior=convertToNull to your JDBC URL:

jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull

This configures JDBC to convert '0000-00-00 00:00:00' values to NULL, allowing you to retrieve them without triggering an exception.

3. zeroDateTimeBehavior Configuration Property

For Connector/J versions 3.1 and above, the zeroDateTimeBehavior property offers more customization. By setting it to exception, convertToNull, or round, you can specify how DATETIME values are handled:

• exception: Throws an SQL exception (default)
• convertToNull: Returns NULL
• round: Rounds the date to 0001-01-01

Refer to the MySQL manual for more details on this configuration property.

Note: In MySQL Connector/J 5.1.15, a bug affected the zeroDateTimeBehavior=convertToNull feature. Consult the official website's CHANGELOGS for updates on bug fixes.

The above is the detailed content of How to Handle '0000-00-00 00:00:00' DATETIME Values in JDBC?. For more information, please follow other related articles on the PHP Chinese website!