Jay, You have a good idea which I think I should be able to incorporate. The syntax that worked for me is -
UPDATE table_name SET `column_id`=NULL WHERE `column_id` = '0000-00-00'; Thanks for the suggestion! David ... On 12/17/2012 7:16 PM, Jay Lozier wrote: > On 12/17/2012 05:35 PM, receiver wrote: >> Girvin's reasoning about dates is quite correct in my opinion. In our >> case the zero valued date fields are logically NULLs and I would prefer >> that the database store them as NULLs instead. I'm afraid it is MySQL >> that is choosing to store zero values. The database in question is >> being loaded with data extracted from other sources. I can get it in >> either CSV or TAB separated format which I then load using phpMyAdmin - >> Import which I believe it utilizing what MySQL refers to as "load data >> infile". Whether I get CSV or TAB separated format there is no data >> (i.e., they are in my opinion NULLs) supplied for these fields which >> phpMyAdmin (or possibly MySQL) is choosing to store as (I'd even say >> convert to) zero values. Therefore, I'm quite happy to have that >> connector convert these fields (back) to NULL. >> >> If there is a way to get MySQL to store NULLs when the imported files >> contain no data I'd love to know about it. I should point out that in >> the case of strings (e.g., fields of type VARCHAR) MySQL will store a >> string of zero length instead of NULL. However, this is much more >> reasonable than storing an invalid value such as a zero date. >> >> David ... > I think the problem is with MySQL not the connector. I see this with > MySQL also. > > A possible work around is after the data has been imported to do the > following: > > UPDATE tablename > SET datefield IS NULL > WHERE datefield = '0000-00-00' > > In principle this should work but I have tried on a database with > '0000-00-00' as date nulls. >> >> On 12/16/2012 4:48 PM, Girvin R. Herr wrote: >>> David, >>> Playing devil's advocate here, I think there may be an inherent >>> systemic problem with zero dates. Most operating systems count time >>> in seconds, or finer, from an arbitrary date, usually the date the >>> operating system was first released or created. All are in the latter >>> 20th century. Since that date is certainly greater than 0000-00-00 no >>> matter which OS you have, such a date is outside the valid range of >>> dates for the system. Additionally, since days and months start at 1, >>> then a day and/or month of 00 is invalid also. Maybe that is why such >>> a date produces a fatal error, while a null date is fine, since null >>> means the date was never set. Although one may assume so, 0000-00-00 >>> is _not_ null! >>> It would be an interesting exercise to test if the year, month, or day >>> being 0 produces the error. >>> Just my 2-cents. >>> >>> Glad to hear you got it working. >>> Girvin Herr >>> >>> >>> >>> receiver wrote: >>>> It works!!! The fact that this rather ugly bit of coding is confined >>>> to the .odb file is the good news. It can be out of sight and out of >>>> mind for my technically challenged end user community. >>>> >>>> Many thanks for a pretty useful tip. >>>> >>>> For the record I'm testing with LO Base 3.6.4, MySQL Connector J >>>> 5.1.22, MySQL Server 5.5.16 >>>> >>>> David ... >>>> >>>> On 12/15/2012 6:10 PM, Girvin R. Herr wrote: >>>>> receiver wrote: >>>>>> I'm trying to create a LibreOffice (3.6.4) Base document which >>>>>> utilizes a MySQL database. I have installed the MySQL Connector J >>>>>> (5.1.22) and have good results with one significant exception. The >>>>>> subject error prevents display of a table which does contain dates. >>>>>> I suspect that this may be a problem with the JDBC connector and >>>>>> have reported it here >>>>>> <http://forums.mysql.com/read.php?39,576155,576155#msg-576155>, >>>>>> however it also seems possible that this is caused by LibreOffice >>>>>> Base. >>>>>> >>>>>> Any advice on what I may have overlooked would be appreciated. >>>>>> >>>>>> David ... >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> View this message in context: >>>>>> http://nabble.documentfoundation.org/Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024569.html >>>>>> >>>>>> >>>>>> Sent from the Users mailing list archive at Nabble.com. >>>>> David, >>>>> Try this: >>>>> Edit -> Database -> Properties >>>>> Select the "Advanced Properties" tab. >>>>> Under "Name of the MySQL database", enter: >>>>> >>>>> <database_name>?zeroDateTimeBehavior=convertToNull >>>>> >>>>> where <database_name> is the name of your database. No spaces >>>>> anywhere. >>>>> Then click OK. >>>>> >>>>> You may have to close and reopen LO/Base for this to take effect. >>>>> This is the string that LO/Base passes to the MySQL connector to >>>>> open the database. >>>>> >>>>> I had the same problem with zero dates and times when I read about >>>>> this workaround years ago. I was not sure if it is still a problem >>>>> with the later versions, but it sounds like it is by your posting. >>>>> LO 3.5.7.2, MySQL 5.0.67, mysql-connector-java-5.0.7 >>>>> >>>>> Hope this helps. >>>>> Girvin Herr >>>>> >>>> >>>> >>>> >>>> -- >>>> View this message in context: >>>> http://nabble.documentfoundation.org/Fwd-Re-libreoffice-users-Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024589.html >>>> >>>> >>>> Sent from the Users mailing list archive at Nabble.com. >> >> >> >> >> -- >> View this message in context: >> http://nabble.documentfoundation.org/Re-Fwd-Re-libreoffice-users-Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4024860.html >> Sent from the Users mailing list archive at Nabble.com. > > -- View this message in context: http://nabble.documentfoundation.org/Re-Fwd-Re-libreoffice-users-Value-0000-00-00-can-not-be-represented-as-java-sql-Date-tp4025045.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
