Re: Handling String date format "0000-00-00 00:00:00" removed in latest version???
Posted by: Bob Montgomery
Date: February 28, 2005 04:02PM

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() + "'";

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.