I get an exception (see below) if I try to do
resultset.getString("add_date");
for a JDBC connection to a MySQL database containing a DATETIME value of 0000-00-00 00:00:00 (the quasi-null value for DATETIME), even though I'm just trying to get the value as string, not as an object.
I got around this by doing
SELECT CAST(add_date AS CHAR) as add_date
which works, but seems silly... is there a better way to do this?
My point is that I just want the raw DATETIME string, so I can parse it myself as is.
note: here's where the 0000 comes in: (from http://dev.mysql.com/doc/refman/5.0/en/datetime.html)
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00').
The specific exception is this one:
SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP. SQLState: S1009 VendorError: 0 java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.ResultSetImpl.getTimestampFromString(ResultSetImpl.java:6343) at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5670) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5491) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)
The obvious way to fix the error is to change the formatting of your value into the format that MySQL can accept. But rather than editing the value manually, you can use the STR_TO_DATE() function to help you convert the string value into date value.
Under Connector/J 8.0, when using zeroDateTimeBehavior=ROUND, zero TIME columns are returned as java.sql.Time with hours/minutes/seconds as zero. - Under Connector/J 8.0, when using zeroDateTimeBehavior=EXCEPTION (the default), zero TIME columns are returned as java.sql.Time with hours/minutes/seconds as zero.
The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.
Use SimpleDateFormat. parse() to parse your date string into a Date object and store that or the getTime() of that in the database. Here's an example of parsing the date: String pattern = "MM/dd/yyyy"; SimpleDateFormat format = new SimpleDateFormat(pattern); Date date = format.
Alternative answer, you can use this JDBC URL directly in your datasource configuration:
jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull
Edit:
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.
I stumbled across this attempting to solve the same issue. The installation I am working with uses JBOSS and Hibernate, so I had to do this a different way. For the basic case, you should be able to add zeroDateTimeBehavior=convertToNull
to your connection URI as per this configuration properties page.
I found other suggestions across the land referring to putting that parameter in your hibernate config:
In hibernate.cfg.xml:
<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
In hibernate.properties:
hibernate.connection.zeroDateTimeBehavior=convertToNull
But I had to put it in my mysql-ds.xml file for JBOSS as:
<connection-property name="zeroDateTimeBehavior">convertToNull</connection-property>
Hope this helps someone. :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With