• 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

Inserting and selecting uncertain dates (eg 2001-00-00)

 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I'm inserting and selecting dates into a MySQL database from jsp pages. However, some of the dates are uncertain - I don't know the month or day - so, for example I would like to insert the date as 2001-00-00 when I know the appropriate date is in 2001 but I don't know the month or day. MySQL recognises this as a valid date format for a DATE field and I can insert / select dates like this directly into MySQL. However, as soon as I start to insert these dates from a jsp page, the date is changed to an actual date (so 2001-00-00 gets inserted as 2000-11-30) and even if I directly correct the data in the database, when I select the date again from a jsp page, it is incorrect (again 2000-11-30) even though it is correct in the database.
I'm guessing that this a problem to do with automatic type conversion within JDBC and java.sql.Date not allowing 00 as a month / date value. Any suggestions as to a workaround / solution for both inserting and selecting these types of dates would be gratefully received.

Thanks in advance,
Kathy
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Kathy,
You may be able to use getString() instead of getDate() to avoid the auto-conversion. However, note that you are relying on database and driver specific functionality that may change in the future.

At some point, you may want to refactor your database to store the date/month in separate fields so they can be null and assemble the date yourself.
 
Kathy Rogers
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Jeanne,

Thanks for the suggestion. I've tried getString but the autoconversion still happens - I suspect getString effectively calls a toString on a java.sql.Date object, rather than getting a String from the database.

I think you've identified the core of the problem when you point out I'm using database specific functionality, although within the context of this quite limited project, that seems fairly safe. The payoff for us of using the date field is that we can use the build-in SQL date functionality (to select within date ranges, for example).

The only solution I can see at the moment to the problem of selecting the date is to change my SQL so that I select the date as a String. What I'd really like is a way of specifying what object the JDBC uses to represent the Date field so that I could use a customised date class which allows these types of dates. Anyone know a way to do this?

Thanks again for your help,

Kathy
 
He was expelled for perverse baking experiments. This tiny ad is a model student:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic