java 将 DATETIME 插入数据库时如何修复错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4953099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to fix error when inserting DATETIME into db
提问by novicePrgrmr
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:
这里有更多的代码。现在我需要设置日期吗？或者 setString 是否适用于开始时间、结束时间和日期？它们都是 DATETIME 对象：
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();
回答by Aravind Yarram
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 docand then use the below code
我建议您使用 PreparedStatement 的 setTimestamp(...) 和 setDate(...) 方法，然后将实际的 Date 对象传递给它们，而不是使用字符串。如果您仅限于在“pr”类中使用字符串，则将字符串转换为derby 文档中指定的格式，然后使用以下代码
java.sql.Timestamp.valueOf("time/date converted");
回答by Dave G
From what I can tell, derby has no "DATETIME" data type, they support DATE, TIME, and TIMESTAMP.
据我所知，derby 没有“DATETIME”数据类型，它们支持 DATE、TIME 和 TIMESTAMP。
http://db.apache.org/derby/docs/10.10/ref/crefsqlj31068.html
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.
其次，我会单引号列名“日期”，因为 DATE 是保留数据类型。我不知道这是否是这里的问题，但可能想尝试一下。
Thirdly, when setting/getting the 'date' column, I would try to use set/getTimestamp and pass/assign a java.sql.Timestamp object where applicable.
第三，在设置/获取“日期”列时，我会尝试使用 set/getTimestamp 并在适用的情况下传递/分配 java.sql.Timestamp 对象。
Hope that provides some help.
希望能提供一些帮助。
回答by Lev Khomich
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.
日期、时间和时间戳不能在表达式中相互混合。Derby 支持以下 TIMESTAMP 格式：
yyyy-mm-dd hh:mm:ss[.nnnnnn]
yyyy-mm-dd-hh.mm.ss[.nnnnnn]年份必须始终为四位数字。月、日和小时可能有一位或两位数字。分和秒必须有两位数。纳秒（如果存在）可能介于 1 到 6 位数字之间。
So, you should replace 'T' with space or hyphen.
因此，您应该用空格或连字符替换“T”。
回答by arcy
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.
如果其他人遇到这个几个月前的问题（就像我所做的那样）执行类似但不相同的操作：仔细查看 Lev Khomich 回答中的两种格式：天/小时之间有一个空格，小时/分钟之间有冒号, 天/小时之间的破折号，小时/分钟之间的句点。我刚刚遇到了一个案例，使用了一个时/分之间的 SQL 语句，其中日/小时之间的空格会导致错误，但破折号不会。
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.
我习惯了 DB2，它在使用小时/分钟和分钟/秒之间的时间段时接受破折号或空格，使其更容易被忽略。我只是假设在天/小时之间允许使用空格或破折号。
回答by Enrico Giurin
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
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')");
