handling DATETIME values 0000-00-00 00:00:00 in JDBC

by

Alternative answer, you can use this JDBC URL directly in your datasource configuration:

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

Edit:

Source: MySQL Manual

Datetimes with all-zero components (0000-00-00 …) — These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the zeroDateTimeBehavior configuration property. The allowable values are:

  • exception (the default), which throws an SQLException with an SQLState of S1009.
  • convertToNull, which returns NULL instead of the date.
  • round, which rounds the date to the nearest closest value which is 0001-01-01.

Update: Alexander reported a bug affecting mysql-connector-5.1.15 on that feature. See CHANGELOGS on the official website.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  3. java.util.Date vs java.sql.Date
  4. Timezones in SQL DATE vs java.sql.Date
  5. How to get the insert ID in JDBC?
  6. Is java.sql.Timestamp timezone specific?
  7. How do I get the size of a java.sql.ResultSet?
  8. How can I get the SQL of a PreparedStatement?
  9. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  10. Variable column names using prepared statements
  11. MySQL and JDBC with rewriteBatchedStatements=true
  12. “EEE MMM dd HH:mm:ss ZZZ yyyy” date format to java.sql.Date
  13. right syntax to use near ‘?’
  14. Running a .sql script using MySQL with JDBC
  15. Java Date – Insert into database
  16. JDBC Pagination
  17. Should I use java.util.Date or switch to java.time.LocalDate
  18. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  19. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  20. Java – Can’t use ResultSet after connection close
  21. Queries returning multiple result sets
  22. SQL prepared statement how to select via multiple possible menu selections?
  23. Class.forName(JDBC_DRIVER) no longer needed?
  24. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  25. How to convert TimeStamp to Date in Java?
  26. Java – Storing SQL statements in an external file [closed]
  27. How to use prepared statement for select query in Java?
  28. java sql date time
  29. MS SQL Exception: Incorrect syntax near ‘@P0’
  30. Database independence through JDBC in java

Leave a Comment