ORA-01830：转换整个输入字符串之前结束的日期格式图片/选择日期查询的和 [英] ORA-01830: date format picture ends before converting entire input string / Select sum where date query

问题描述

我尝试通过Java执行我的查询：

I try to execute my query through Java like this:

public ResultSet execSumStatment2() throws SQLException{
String query = "Select SUM(A) as NCCSeptember from NCC where Datum >= '01-09-2013 00:00:00' and Datum <= '30-09-2013 23:59:59'";
return execStatement(query);
}

然后我在类中调用execSumStatement：

Then i call execSumStatement in the class:

sql.execSumStatement2 () ;

当我运行它时，我收到以下错误消息：

When i run it i receive following error message:

java.sql.SQLDataException：ORA-01830：转换整个输入字符串之前结束的日期格式图片

java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string

oracle.jdbc.driver.T4CTTIoer.processError（T4CTTIoer.java:447）
at oracle.jdbc.driver.T4CTTIoer.processError（T4CTTIoer.java:396）

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
at com.bachelorarbeit.SQLExecutor.execStatement(SQLExecutor.java:20)
at com.bachelorarbeit.SQLExecutor.execSumStatment2(SQLExecutor.java:56)
at com.bachelorarbeit.Test.doGet(Test.java:63)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

当我在SQL Developer中复制/粘贴查询时，工作正常。我已经在互联网上搜索，我猜这个问题是查询中日期的数据类型，这应该是Date，但是我试过没有工作...

When i copy/paste the query in SQL Developer it works just fine. I already search on the internet, and i guess the problem is the datatype of the date in the query, which should be Date, but everithing i tried didn t work...

我是初学者的编程，所以如果你解释尽可能简单，这将是伟大的！感谢提前！

I m a beginner in programming so it would be great if you explain it as simple as possible ! Thanks in advance !

推荐答案

我认为你不应该依赖于隐式转换。这是一个不好的做法。

I think you should not rely on the implicit conversion. It is a bad practice.

相反，你应该这样尝试：

Instead you should try like this:

datenum >= to_date('11/26/2013','mm/dd/yyyy')

或类似

datenum >= date '2013-09-01'

这篇关于ORA-01830：转换整个输入字符串之前结束的日期格式图片/选择日期查询的和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！