• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

mysql date problem

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)
);
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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'.
 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 401
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic