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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use this JDBC URL directly in your data source configuration      jdbc mysql   yourserver 3306 yourdatabase zeroDateTimeBehavior convertToNull

User · Answer

you can try like This  ArrayList lt String gt  dtlst   new ArrayList lt String gt     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

just cast the field as char  Eg  cast updatedate  as char as updatedate

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to mysql

Examples related to jdbc

Examples related to timestamp