• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mysql date problem

 
seema prakash
Ranch Hand
Posts: 59
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33700
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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'.
 
Manish Sridharan
Ranch Hand
Posts: 65
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
James Hodgkiss
Ranch Hand
Posts: 401
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic