'0000-00-00 00:00:00' can not be represented as java.sql.Timestamp error

Question

I have a database table containing dates

 (`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'). 

I'm using MySQL. From the program sometimes data is passed without the date to the database. So, the date value is auto assigned to 0000-00-00 00:00:00 when the table data is called with the date column it gives error

...'0000-00-00 00:00:00' can not be represented as java.sql.Timestamp.......

I tried to pass null value to the date when inserting data, but it gets assign to the current time.

Is there any way I can get the ResultSet without changing the table structure?

Solution 1

You can use this JDBC URL directly in your data source configuration:

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

Solution 2

Whether or not the "date" '0000-00-00" is a valid "date" is irrelevant to the question. "Just change the database" is seldom a viable solution.

Facts:

• MySQL allows a date with the value of zeros.
• This "feature" enjoys widespread use with other languages.

So, if I "just change the database", thousands of lines of PHP code will break.

Java programmers need to accept the MySQL zero-date and they need to put a zero date back into the database, when other languages rely on this "feature".

A programmer connecting to MySQL needs to handle null and 0000-00-00 as well as valid dates. Changing 0000-00-00 to null is not a viable option, because then you can no longer determine if the date was expected to be 0000-00-00 for writing back to the database.

For 0000-00-00, I suggest checking the date value as a string, then changing it to ("y",1), or ("yyyy-MM-dd",0001-01-01), or into any invalid MySQL date (less than year 1000, iirc). MySQL has another "feature": low dates are automatically converted to 0000-00-00.

I realize my suggestion is a kludge. But so is MySQL's date handling. And two kludges don't make it right. The fact of the matter is, many programmers will have to handle MySQL zero-dates forever.

Solution 3

Append the following statement to the JDBC-mysql protocol:

?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

for example:

jdbc:mysql://localhost/infra?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&characterSetResults=UTF-8

Solution 4

Instead of using fake dates like 0000-00-00 00:00:00 or 0001-01-01 00:00:00 (the latter should be accepted as it is a valid date), change your database schema, to allow NULL values.

ALTER TABLE table_name MODIFY COLUMN date TIMESTAMP NULL

Solution 5

As an exteme turnaround, when you cannot do an alter to your date column or to update the values, or while these modifications take place, you can do a select using a case/when.

SELECT CASE ModificationDate WHEN '0000-00-00 00:00:00' THEN '1970-01-01 01:00:00' ELSE ModificationDate END AS ModificationDate FROM Project WHERE projectId=1;

View more solutions

Comments

[botenvouwer]

Yes there is a year 0000. infact its year 0 and we have a year -1 and a year -1000. Never saw this Gregorian calendar or this Anno Domini and more especialy the Gregorian calendar does not have a year zero but the iso has and the iso is used bij computers see 0 year

[Vishy]

@sirwilliam Thank you for that interesting qualification. It appears the OP wanted year 0 to be treated as a NULL or something which doesn't exist.

[Thufir]

curious. Reading the other answers, duh, there's no month zero. What does does this really mean?

[Juha Palomäki]

In MySQL 0000-00-00 00:00:00 is equal to 0. In legacy code there might be some queries which rely on this.

[Techmag]

Be careful with this approach. It works like a charm but it took down a production server on us (worked perfectly in dev though...) . What we learned is that you'll need to quote the string as the & is interpreted as a special character in some contexts giving the line a completely different meaning...

[jeffkempf]

Do you know if there's a MariaDB equivalent to append to my jdbc URL? jdbc:mariadb://localhost:3306/dev?zeroDateTimeBehavior=conve‌​rtToNull doesn't seem to work for me.

[Sunil Sharma]

this also wont help.

[routeburn]

Had to be CONVERT_TO_NULL for me