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.
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.
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?