This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 159197

Summary: SQL editor cannot display dates with value 0000-00-00
Product: db Reporter: dkkeen <dkkeen>
Component: MySQLAssignee: Jiri Rechtacek <jrechtacek>
Status: RESOLVED WONTFIX    
Severity: blocker CC: rperazzo
Priority: P3    
Version: 6.x   
Hardware: PC   
OS: Linux   
Issue Type: DEFECT Exception Reporter:

Description dkkeen 2009-02-26 10:02:38 UTC
MySQL allows you to store '0000-00-00' as a “dummy date”.

When trying to view data that includes dates with this value using the SQL editor the following error occurs:
"Error code 0, SQL state S1009: Value '0000-00-00' can not be represented as java.sql.Date"
Comment 1 Jiri Rechtacek 2009-07-16 20:25:04 UTC
It's a known problem in MySQL, need to set a special parameter in MySQL driver - http://bugs.mysql.com/bug.php?id=13261.
With this parameter is works better.
Comment 2 Jiri Skrivanek 2009-09-14 14:41:50 UTC
Please, use zeroDateTimeBehavior=convertToNull while connecting to your databse. E.g.:

jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull
Comment 3 Jiri Skrivanek 2009-09-15 10:28:23 UTC
*** Issue 157309 has been marked as a duplicate of this issue. ***
Comment 4 Jiri Rechtacek 2009-10-22 19:11:26 UTC
*** Issue 175226 has been marked as a duplicate of this issue. ***
Comment 5 podolinek 2009-10-22 21:54:10 UTC
Omg, thanks a lot. very obscured reason:-( I tried to update Connector/J on version 5.1.11 because on
http://bugs.mysql.com/bug.php?id=13261 is info the patch is commited (from july 2008) but still not repaired:( Thanks
for help especialy jskrivanek. Very good support. My first report here and I hope no last. NetBeans is fine tool:)
--
podolinek
Comment 6 Jiri Rechtacek 2010-12-21 10:27:01 UTC
*** Bug 193562 has been marked as a duplicate of this bug. ***
Comment 7 wernerm 2012-05-24 15:42:39 UTC
The zeroDateTimeBehavior=convertToNull solution should be documented somewhere or be supplied as a connection / service-property option?
Comment 8 matthias42 2012-05-24 17:43:42 UTC
The current template for mysql jdbc urls leads to this:

jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull

The mentioned parameter is now part of the default url. No need to add something by hand. FYI this was checked against a build of core-main from a few days ago.
Comment 9 luislobo 2012-06-07 23:40:46 UTC
The problem is that it throwed an exception. Shouldn't it be handled more gracefully?