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();

I have tried that, but I get error lines saying, "unclosed character literal, not a statement, ';' excpected. I am pretty stumped here and it seems so simple — novicePrgrmr
– novicePrgrmr, Commented Feb 10, 2011 at 3:44
then we need to see the code for pr class and also the jdbc code — Aravind Yarram
– Aravind Yarram, Commented Feb 10, 2011 at 3:57

Aravind Yarram · Accepted Answer · 2011-02-10 05:35:31Z

6

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");

answered Feb 10, 2011 at 5:35

Aravind Yarram

80.5k49 gold badges239 silver badges335 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bryan Pendleton Over a year ago

Thus: ps.setTimestamp(Timestamp.valueOf("2006-09-10 00:00:00"));

bakoyaro · Accepted Answer · 2013-09-12 02:12:19Z

4

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.

edited Sep 12, 2013 at 2:12

bakoyaro

2,5583 gold badges36 silver badges63 bronze badges

answered Feb 10, 2011 at 7:37

Dave G

9,82238 silver badges43 bronze badges

Comments

Lev Khomich · Accepted Answer · 2011-02-10 04:03:26Z

2

Dates, times, and timestamps cannot be mixed with one another in expressions. Derby supports the following formats for TIMESTAMP:

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

The year must always have four digits. Months, days, and hours may have one or two digits. Minutes and seconds must have two digits. Nanoseconds, if present, may have between one and six digits.

So, you should replace 'T' with space or hyphen.

answered Feb 10, 2011 at 4:03

Lev Khomich

2,24714 silver badges18 bronze badges

Comments

arcy · Accepted Answer · 2012-06-10 11:46:39Z

1

In case someone else runs across this months-old question (as I did) doing a similar but not identical operation: Look carefully at the two formats in Lev Khomich's answer: there is a space between day/hour with colons between the hour/min, and a dash between day/hour with periods between hour/min. I just had a case, using a SQL statement with periods between hour/min, where a space between day/hour caused an error but a dash did not.

I'm used to DB2, which accepts either a dash or a space when using periods between hour/min and min/sec, making it even easier to overlook. I was just assuming either space or dash was allowed between day/hour.

answered Jun 10, 2012 at 11:46

arcy

13.2k16 gold badges66 silver badges117 bronze badges

2 Comments

user907810 Over a year ago

I get error that the timestamp is not in the right format: can you please tell me what is wrong? Here is my INSERT stt: INSERT INTO APP.MYTABLE VALUES ('2012-09-10 08:32:22[.1020]');

arcy Over a year ago

I don't have any testing environment set up to test this, but I know of no DB that allows the square brackets in the date format. The square brackets are fairly standard notation in describing the format, indicating that the portion within the brackets is optional. But I think your problem is likely that the brackets cannot actually appear. Just take out [ and ].

Enrico Giurin · Accepted Answer · 2016-12-12 01:05:41Z

0

I had the same issue, then I found the solution here:

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

Basically you have to 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')");

answered Dec 12, 2016 at 1:05

Enrico Giurin

2,28133 silver badges31 bronze badges

Collectives™ on Stack Overflow

how to fix error when inserting DATETIME into db

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related