I am trying to query SQL data with MATHEMATICA - however, the MySQL database contains zerodates and that leads to the following error message:

e.g. (1) *SQLSelect[conn, "orders", {"time_created"}, MaxRows -> 5]* --> JDBC::error: "!(\"Cannot convert value '0000-00-00 00:00:00' from column 1 to TIMESTAMP.\")"

e.g. (2) *SQLExecute[conn, "SELECT * FROM orders", MaxRows -> 5]* --> JDBC::error: "!(\"Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP.\")"

I found out that this is a typical issue with a JDBC connection and that this can be solved by allowing zerodatetimes as true with examples from other systems. What I didn't find out is: How to apply this specifically in Mathematica? Can you advise me?

It would help me a lot - so thank you very much for any help!


1 Answer 1


I hope the following helps, it worked for me (I used it once, but then converted dates and thus got rid of those zero dates didn't use it anymore, so I hope it's somewhat stable...).

What I did is specify the property when setting up the connection, i.e. if using the wizard to set up the connection, I added ?zeroDateTimeBehavior=convertToNull to the database name, see screenshot:

enter image description here

where DB is your database. When doing it directly (without wizard), it will be something like:

conn = OpenSQLConnection[
   Username -> "Hello", Password -> "Kitty"]

As a consequence, all "zerodates" will show up as "Null".


Please see the following link for more detailed information (and other settings/parameters) - I figured this might be of interest as well (choose mysql version top left):

mySQL documentation

  • $\begingroup$ Very helpful - I added ?zeroDateTimeBehavior=convertToNull at the end of the JDBC Connection and it works perfectly. Thanks! $\endgroup$ Feb 8, 2013 at 9:02
  • $\begingroup$ Great, glad it helps. Please see the link I added for more information on parameters (I never used any others, but well, you might... ) $\endgroup$ Feb 8, 2013 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.