Two Laptop Bag*
The moose likes JDBC and the fly likes java.sql.Date '0000-00-00' problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.Date Watch "java.sql.Date New topic
Author

java.sql.Date '0000-00-00' problem

Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Hi,

I run into this problem: Value ‘0000-00-00’ can not be represented as java.sql.Date; In some cases there is no date to input in the database and the input is null. Is there any way to solve this?

Thank you,
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60740
    
  65

Why not just use a null?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

When I pasted the error message into Google I got more than one page of results. The first four results from the top of my list all had the same recommendation. Here's the most concise version: http://community.eapps.com/showthread.php?8-Value-0000-00-00-cannot-be-represented-as-java.sql.Date.
Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Thanks. My editbale JTable returns null if there is not a date value in the database; and I store null in the database for any missing data; but I get the error message as if I had stored "0000-00-00" or as if the table returned "0000-00-00"

I am going to try to understand this: The JDBC driver developers for MySQL created an option for how '0000-00-00' dates should be handled (since that technically isn't a valid date from Java's standpoint). Even though this is the default value for NOT NULL date fields in MySQL, they decided to default this option to throw an exception. You can in fact change this behavior to the more sensible option of just returning null. To do this, add "zeroDateTimeBehavior=convertToNull" to the end of the "url" parameter in the server.xml connection pool resource section. Here's what the end product should look like:


Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Yes. You are rigth guys. I made a change in my JTable and it accidentaly change the database value from null to "0000-00-00"; the prole is with the JTable then. Do you handle JTable questions as well or showld I pose the question to the SWING guys?

Than you
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60740
    
  65

Swing questions should be posted in the Swing forum.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: java.sql.Date '0000-00-00' problem
 
Similar Threads
Why it put NULL valuse in my field?
SimpleDateFormat
Specify Date as 0000-00-00
Java date to MySQL date conversion
NULL value problem!!!