用于Sql插入的Java日期

Question

我试图在oracle数据库中插入一个日期，但我一直面临着一个转换问题。

public List<Customer> getCustomerData(Date start, Date end) {
...
String query = "SELECT name FROM customers WHERE trunc(order_timestamp) between trunc(?) and trunc(?)";

DateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
pst.setDate(1, new java.sql.Date(sdf.parse(sdf.format(start)).getTime()));

pst.setDate(2,  new java.sql.Date(sdf.parse(sdf.format(end)).getTime()));

}

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
Date start = sdf.parse("10/10/2014");
Date end = sdf.parse("10/10/2016");
customerDao.getCustomerData(start,end);

Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "2014-10-10"; SQL statement:
select name from customers where trunc(order_timestamp) between trunc(?) and trunc(?)  [22018-160]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.value.Value.convertTo(Value.java:852)
    at org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:366)
    at org.h2.expression.JavaFunction.getValue(JavaFunction.java:38)
    at org.h2.expression.Comparison.getValue(Comparison.java:206)
    at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:83)
    at org.h2.expression.ConditionAndOr.getValue(ConditionAndOr.java:90)
    at org.h2.expression.Expression.getBooleanValue(Expression.java:180)
    at org.h2.command.dml.Select.queryFlat(Select.java:514)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:617)
    at org.h2.command.dml.Query.query(Query.java:298)
    at org.h2.command.dml.Query.query(Query.java:268)
    at org.h2.command.dml.Query.query(Query.java:37)
    at org.h2.command.CommandContainer.query(CommandContainer.java:82)
    at org.h2.command.Command.executeQuery(Command.java:185)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)
    at com.customerDaoImpl.custDao.getParentContextdata(custDao.java:431)
    ... 29 more
Caused by: java.lang.NumberFormatException: For input string: "2014-10-10"
    at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1222)
    at java.lang.Double.parseDouble(Double.java:510)
    at org.h2.value.Value.convertTo(Value.java:831)
    ... 44 more

drop ALIAS if exists TRUNC;
CREATE ALIAS TRUNC as '
import java.text.*;
@CODE
double trunc(double num) throws Exception {
    return Math.floor(num*10) /10;
}
';

Answer 1

堆栈跟踪显示，您根本没有使用Oracle。你用的是H2。而且H2似乎没有任何trunc函数接受日期作为参数。

无论如何，这个函数调用都是没用的，因为您已经传递了一个日期，没有任何时间部分作为参数。如果trunc操作存在，它将什么也不做。