英文:
Getting ORA-01858 error while inserting time stamp
问题
我的前端时间戳格式如下：
2020-7-23 14:36:43.132000000
我的 Oracle 数据库中的时间戳格式如下：
23-07-20 02:36:43.132000000 PM
我已经更改了时间格式如下：
private String OracleTsString(String createdDate) {
SimpleDateFormat inputFormat = null;
SimpleDateFormat outputFormat = null;
try {
inputFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss.SSSSSSSSS");
Date date;
date = inputFormat.parse(createdDate);
outputFormat = new SimpleDateFormat("MM-dd-yy hh:mm:ss.SSSSSSSSS aa");
}
// 异常处理
}
但是当我尝试将字符串插入数据库时，我收到了 ORA-01858: 非数字字符出现在应该是数字的位置** 的错误。
以下是我的查询：
jdbcTemplate.update(query,
editSftpBean.getSftpName(),
editSftpBean.getIp_address(),
editSftpBean.getPort_number(),
editSftpBean.getUserName(),
editSftpBean.getAuthentication(),
oracleTs//我的转换后的字符串
);
插入查询：
INSERT INTO tabl_two (SFTP_NAME, IP_ADDRESS, PORT_NUMBER, USER_NAME, AUTH_TYPE, CREATED_DATE) VALUES (?, ?, ?, ?, ?)
我该如何解决这个问题？
英文:
My timestamp format in front end is like this
2020-7-23 14:36:43. 132000000
My timestamp in oracle db is like this
23-07-20 02:36:43.132000000 PM
I have changed the time format like below
private String OracleTsString(String createdDate) {
SimpleDateFormat inputFormat=null;
SimpleDateFormat outputFormat = null;
try {
inputFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss.SSSSSSSSS");
Date date;
date = inputFormat.parse(createdDate);
outputFormat = new SimpleDateFormat("MM-dd-yy hh:mm:ss.SSSSSSSSS aa");
}
//Exception Handling
But when I try to insert the string into database , I am getting ORA-01858: a non-numeric character was found where a numeric was expected
Below is my query
jdbcTemplate.update(query,
editSftpBean.getSftpName(),
editSftpBean.getIp_address(),
editSftpBean.getPort_number(),
editSftpBean.getUserName(),
editSftpBean.getAuthentication(),
oracleTs//My converted string
);
insert query::
INSERT INTO tabl_two (SFTP_NAME, IP_ADDRESS, PORT_NUMBER, USER_NAME, AUTH_TYPE,CREATED_DATE ) VALUES (?, ?, ?, ?, ?)
How can I resolve this?
答案1
得分: 3
不要使用String将日期/时间值插入数据库，而要使用Timestamp。
private static Timestamp parseTsString(String createdDate) {
DateTimeFormatter fmt = new DateTimeFormatterBuilder()
.appendPattern("uuuu-M-d H:mm:ss")
.appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true)
.toFormatter();
return Timestamp.valueOf(LocalDateTime.parse(createdDate, fmt));
}
英文:
Don't use a String to insert a date/time value into the database, use a Timestamp.
private static Timestamp parseTsString(String createdDate) {
DateTimeFormatter fmt = new DateTimeFormatterBuilder()
.appendPattern("uuuu-M-d H:mm:ss")
.appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true)
.toFormatter();
return Timestamp.valueOf(LocalDateTime.parse(createdDate, fmt));
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论