Re: Handling String date format "0000-00-00 00:00:00" removed in latest version???

[Bob Montgomery]

I had a similar problem except that I was getting java bean failures on java server pages when the record I was accessing contained a datetime field containing '0000-00-00 00:00:00' or a date field containing '0000-00-00'. The strange thing was that I was not referencing the date fields in my queries.

My resolution was to modify each date field in each table as shown (using a more recent, but wrong in my context date):

ALTER TABLE `TABLE1` CHANGE `$DTA` `$DTA` DATETIME DEFAULT '1900-01-01 00:00:00' NOT NULL;
UPDATE `TABLE1` SET $DTA = '1900-01-01 00:00:00' WHERE $DTA = '0000-00-00 00:00:00';

I'm hoping that by setting the fields to NOT NULL and providing a default, I can avoid having more nulls creep into my tables (which will blow up my pages again). My concern is that I now have an extensive amount of reprogramming to do on those fields where I would like the date field to remain "blank".

*********************************

I make extensive use of PreparedStatement and have not experienced the behavior you indicated. However, instead of using question marks, I tend to build my queries dynamically like this:

String query = "SELECT * FROM TABLE WHERE FIELD = " + getNumericFieldValue();

or

String query = "SELECT * FROM TABLE WHERE FIELD = '" + getAlphaFieldValue() + "'";