'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?

score 339 · Accepted Answer · answered Jun 21 '12 at 08:00

339

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

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

answered Jun 21 '12 at 08:00

Kushan

10,657
4
37
41

2

curious. Reading the other answers, duh, there's no month zero. What does does this really mean? – Thufir Aug 10 '14 at 00:04
4

Do you know if there's a MariaDB equivalent to append to my jdbc URL? jdbc:mariadb://localhost:3306/dev?zeroDateTimeBehavior=convertToNull doesn't seem to work for me. – jeffkempf Mar 01 '17 at 19:02
Had to be CONVERT_TO_NULL for me – routeburn Oct 23 '18 at 20:23

score 20 · Answer 2 · edited Jul 23 '15 at 21:58

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.

score 11 · Answer 3 · edited Jan 24 '15 at 00:37

11

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

edited Jan 24 '15 at 00:37

Air

8,274
2
53
88

answered Jun 06 '14 at 05:03

Yogesh H Bhosale

365
3
8

5

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... – Techmag Jun 10 '16 at 20:53

score 9 · Answer 4 · answered Jun 21 '12 at 07:59

9

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

answered Jun 21 '12 at 07:59

npe

15,395
1
56
55

score 6 · Answer 5 · answered Aug 10 '15 at 15:28

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;

score 2 · Answer 6 · answered Dec 20 '17 at 08:18

you can try like This

ArrayList<String> dtlst = new ArrayList<String>();
String qry1 = "select dt_tracker from gs";

Statement prepst = conn.createStatement();
ResultSet rst = prepst.executeQuery(qry1);
while(rst.next())
{
    String dt = "";
    try
    {
        dt = rst.getDate("dt_tracker")+" "+rst.getTime("dt_tracker");
    }
    catch(Exception e)
    {
        dt = "0000-00-00 00:00:00";
    }

    dtlst.add(dt);
}

score 1 · Answer 7 · answered Jan 15 '13 at 23:33

I wrestled with this problem and implemented the URL concatenation solution contributed by @Kushan in the accepted answer above. It worked in my local MySql instance. But when I deployed my Play/Scala app to Heroku it no longer would work. Heroku also concatenates several args to the DB URL that they provide users, and this solution, because of Heroku's use concatenation of "?" before their own set of args, will not work. However I found a different solution which seems to work equally well.

SET sql_mode = 'NO_ZERO_DATE';

I put this in my table descriptions and it solved the problem of '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

Peter Lawrey · Answer 8 · 2013-06-21T02:10:38.363

0

There was no year 0000 and there is no month 00 or day 00. I suggest you try

0001-01-01 00:00:00

While a year 0 has been defined in some standards, it is more likely to be confusing than useful IMHO.

edited Jun 21 '13 at 02:10

answered Jun 21 '12 at 07:53

Peter Lawrey

525,659
79
751
1,130

2

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](https://en.wikipedia.org/wiki/Gregorian_calendar) or this [Anno Domini](http://en.wikipedia.org/wiki/Before_Christ) 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](http://en.wikipedia.org/wiki/0_(year)) – botenvouwer Jun 20 '13 at 22:21
@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. – Peter Lawrey Jun 21 '13 at 02:12
2

In MySQL 0000-00-00 00:00:00 is equal to 0. In legacy code there might be some queries which rely on this. – Juha Palomäki Nov 22 '15 at 10:16

score 0 · Answer 9 · answered Jun 03 '16 at 06:36

0

just cast the field as char

Eg: cast(updatedate) as char as updatedate

answered Jun 03 '16 at 06:36

Santhosh Rajkumar

121
1
3

score 0 · Answer 10 · answered Dec 06 '16 at 07:54

0

I know this is going to be a late answer, however here is the most correct answer.

In MySQL database, change your timestamp default value into CURRENT_TIMESTAMP. If you have old records with the fake value, you will have to manually fix them.

answered Dec 06 '16 at 07:54

PeakGen

21,894
86
261
463

this also wont help. – Sunil Sharma Oct 07 '18 at 19:59

score 0 · Answer 11 · answered Jul 11 '17 at 15:15

0

You can remove the "not null" property from your column in mysql table if not necessary. when you remove "not null" property no need for "0000-00-00 00:00:00" conversion and problem is gone.

At least worked for me.

answered Jul 11 '17 at 15:15

Celik

2,311
2
32
54

score -2 · Answer 12 · answered Mar 27 '17 at 06:45

I believe this is help full for who are getting this below Exception on to pumping data through logstash Error: logstash.inputs.jdbc - Exception when executing JDBC query {:exception=>#}

Answer:jdbc:mysql://localhost:3306/database_name?zeroDateTimeBehavior=convertToNull"

or if you are working with mysql

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

12 Answers12

Linked

Related