File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Inserting and selecting uncertain dates (eg 2001-00-00) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Inserting and selecting uncertain dates (eg 2001-00-00)" Watch "Inserting and selecting uncertain dates (eg 2001-00-00)" New topic

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

Kathy Rogers
Ranch Hand

Joined: Aug 04, 2000
Posts: 104

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,
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33130

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Kathy Rogers
Ranch Hand

Joined: Aug 04, 2000
Posts: 104
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,

I agree. Here's the link:
subject: Inserting and selecting uncertain dates (eg 2001-00-00)
It's not a secret anymore!