my dog learned polymorphism*
The moose likes JDBC and the fly likes mysql date problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "mysql date problem" Watch "mysql date problem" New topic
Author

mysql date problem

seema prakash
Ranch Hand

Joined: Nov 17, 2004
Posts: 59
hi,
I have a table where one of the column is a date.
I am able to get the value entered in the textfield
and insert the value in database using preparedStatement
and setDate(). If the value entered in the texfield is
empty, it enters '0000-00-00' as default in the database.
The problem is I'm not able to retrieve the value for
the date field if the textfield was empty. How do I test
for null values? When I use if(res.getDate(5) == null)
after the query to test for null values I get the exception:

Value '0000-00-00' can not be represented as java.sql.Date

Please help
I'm using mysql 4.0.25 and msql-Connector-java-3.0.11
My create table looks like this:

CREATE TABLE CandidateStatus ( StatusID INT NOT NULL AUTO_INCREMENT
, ResumeID INT NOT NULL REFERENCES
Resumes(ResumeID) ON DELETE CASCADE
, RecruiterName VARCHAR(96) CHECK (RecruiterName IS NOT
NULL)
, Submitted_to_Client VARCHAR(96) DEFAULT ''
, Submission_Date DATE DEFAULT '0000-00-00'
, Submission_Status VARCHAR(20) DEFAULT ''
, PerHourCost INT DEFAULT 0
, PerHourRatePresented INT DEFAULT 0
, PRIMARY KEY (StatusID)
, FOREIGN KEY (ResumeID) REFERENCES Resumes(ResumeID)
);
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

Seema,
I think the thing here is that there isn't a null in the database. There is an invalid date of '0000-00-00'. So testing for null isn't going to help.

If you control the schema, I recommend using a default of null rather than '0000-00-00'. If not, you can get the value as a string using rs.getString(5) and compare it to '0000-00-00'.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Manish Sridharan
Ranch Hand

Joined: Jul 19, 2005
Posts: 64
Seema ! I too experiencing the same problem as u posted . i set default value as null in date field. and things work prety fine after that.
But i have one query that mysql accept date in the format YYYY-MM-DD,and on the front i am accepting date in the dd-mm-yyyy format so do i have to convert it into mysql format thru coding or it will be convert by mysql automatically. please reply it its urgetnt


Manish S.
James Hodgkiss
Ranch Hand

Joined: Jan 22, 2004
Posts: 401
Thought I would re-open this thread as it's a very common problem with little known solution (that I came across today). Append the zeroDateTimeBehavior argument to your JDBC url as follows:

jdbc:mysql://localhost:3306/myDatabase?zeroDateTimeBehavior=convertToNull
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: mysql date problem