• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL JDBC Driver and Null Dates

 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For whatever reason my app has decided to start through SQLExceptions when encountering NULL dates and datetimes. I know that this is intentional as per the driver however I also found a way to tell MySQL to convert zero dates to null. On the connection String you can add:

zeroDateTimeBehavior=convertToNull

In DBVisualizer adding this to the connection string actually works. When I would normally get errors in its console I know longer get those errors. However, in my web application I am still getting the exception. I'm just curious if anyone else has ran into this issue and how they resolved it (other than not allowing null dates).
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Gregg,

I thought convertToNull was to handle MySQL's use of zero for DATE/TIME/DATETIME/TIMESTAMP. The default value for DATE/TIME/DATETIME/TIMESTAMP is zeros (varies by format).

Is the date in the database truly null?

Paul

 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul, yes, that was more or less what i was saying. Just didn't translate correctly. Anyway, I've done some more research and it turns out that the flag is working. However, the column is defined as NOT NULL in the schema. Some of the columns didn't have a date (were null or zero) but since the constraint is NOT NULL it doesn't matter what the flag is set to. An exception is still going to occur. Another date column that allows null values is working fine. I don't know that this discover is fact but from my own conclusions I can't see what else it could be.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Campbell wrote:Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).



My code needs NULL. NULL denotes specific things about these date columns. For example, if the endDate column is null, then something is not complete. This I cannot change as I am working in an existing project and too much code is tied to those values being null.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Gregg Bolinger wrote:

Paul Campbell wrote:Okay, if I'm understanding this correctly... the column is set to not null... so the default for the date/timestamp would be some form of all zeros.

Can your application handle a date using round in your connection instead of convertToNull? This would convert a zero date/timestamp to the nearest jdbc representable
value (i.e., 01-01-0001 00:00:00).



My code needs NULL. NULL denotes specific things about these date columns. For example, if the endDate column is null, then something is not complete. This I cannot change as I am working in an existing project and too much code is tied to those values being null.



Sorry Gregg... I'm slow today.

Okay... now I'm following you... jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull

The convertToNull parameter isn't converting to null when you add it to your jdbc connection for your application.

 
no wonder he is so sad, he hasn't seen this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic