how to fix error when inserting DATETIME into db

Question

I am trying to to do this:

pr.setStartdate("2006-09-10T00:00:00");

I am getting this error:

java.sql.SQLDataException: The syntax of the string representation of a datetime value is incorrect.

any ideas on how to successfully insert would be great.

here is a little more code. now do i need to setDate? or does setString work for begintime, endtime, and date? they are all DATETIME objects:

PreparedStatement pstmt = conn.prepareStatement("UPDATE Event SET date=?, begintime=?, endtime=?, status=?, productionid=?, conceptual_packageid=? WHERE id=?");
        pstmt.setString(1, pkg.getDate());
        pstmt.setString(2, pkg.getBeginTime());
        pstmt.setString(3, pkg.getEndTime());
        pstmt.setString(4, pkg.getStatus());
        pstmt.setString(5, pkg.getProductionID());
        pstmt.setString(6, pkg.getConceptual_PackageID());
        pstmt.setString(7, pkg.getId());

        pstmt.executeUpdate();
        pstmt.close();

Answer 1

I'd suggest you use setTimestamp(...) and setDate(...) methods of PreparedStatement and then pass in the actual Date objects to them rather than working with strings. If you are limited to working with Strings in the "pr" class then convert the Strings to the format specified in the derby doc and then use the below code

java.sql.Timestamp.valueOf("time/date converted");

Answer 2

From what I can tell, derby has no "DATETIME" data type, they support DATE, TIME, and TIMESTAMP.

http://db.apache.org/derby/docs/10.10/ref/crefsqlj31068.html

As such I would make sure you're declaring your 'date' column as a TIMESTAMP if you are wanting to keep both the time and date value in that field.

Second I would single quote the column name 'date' as DATE is a reserved data type. I don't know if that would be the problem here but might want to try it.

Thirdly, when setting/getting the 'date' column, I would try to use set/getTimestamp and pass/assign a java.sql.Timestamp object where applicable.

Hope that provides some help.