How to fix error while inserting datatime in db

Question

I am trying to do this:

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

I get 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 bit more code. now i need setDate? or does setString work for begintime, endtime and date? 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 suggest you use the setTimestamp (...) and setDate (...) PreparedStatement methods, and then pass 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 doc derby , and then use the following code

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

Answer 2

, , DATETIME, DATE, TIME TIMESTAMP.

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

, , "date" TIMESTAMP, , .

-, "", DATE . , , .

-, / "" set/getTimestamp / java.sql.Timestamp, .

, .

Answer 3

, . Derby TIMESTAMP:

yyyy-mm-dd hh: mm: ss [.nnnnnn]
---.. [.nnnnnn]

. , . . , .

, "T" .

Answer 4

, - ( ), , : : ​​ / ​​/ /. , SQL /, ​​ , .

I'm used to DB2, which accepts a dash or space when using periods between hours / min and min / sec, which makes it even easier to skip. I just assumed that a space or dash is allowed between day / hour.

Answer 5

I had the same problem, I found a solution here:

http://apache-database.10148.n7.nabble.com/Date-Timestamp-format-for-inserts-td99979.html

Basically, you should use this format:

yyyy-mm-dd-hh.mm.ss[.nnnnnn]

For instances, this insert works for me:

statement.executeUpdate("INSERT INTO animal VALUES (1, 1, 'Elsa', '2006-09-10-00.00.00')");