Bug #7369 ResultSet.getTimestamp throws exception for 0000-00-00 00:00:00 value

• Submitted: 16 Dec 2004 22:59
• Modified: 16 Dec 2004 23:34
• Reporter: Rich Collins
• Status: Not a Bug
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.1.5-gamma
• OS: Linux (Red Hat)
• CPU Architecture: Any

[16 Dec 2004 22:59] Rich Collins

Description:
I, like many others, use '0000-00-00 00:00:00' to represent a null date value for datetime and timestamp fields in mysql databases. Prior to the 3.1 Connector, calling ResultSet.getTimestamp method would return null.  Now it throws an exception with the stack trace:

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP.
        at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:6362)
        at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6387)
        at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2263)
        at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2295)

If this continues to be the case, I, like many others, will never be able to upgrade to mysql 4.1 as it would be impossible to refactor the code everywhere this basic operation is executed.  Please feel free to email me about this issue as it is of dire consequence to me and my company.

Thanks,

Rich Collins

How to repeat:
just call ResultSet.getTimestamp on a column that has a datetime or timestamp value of 0000-00-00 00:00:00

Suggested fix:
let ResultSet.getTimestamp return null for 0000-00-00 00:00:00 values as it has in all past versions.

[16 Dec 2004 23:34] Mark Matthews

Ref. the documentation, http://dev.mysql.com/doc/connector/j/en/index.html#id2422964 and look at the 'zeroDateTimeBehavior' connection property (i.e. you can choose what you want to have happen when the driver runs into an all-zero date).

If you want the old behavior, use 'convertToNull'.

The current default  behavior is required for both SQL:2003 and JDBC compliance.