oracle ORA-01830：日期格式图片在转换整个输入字符串之前结束/选择日期查询的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20219363/
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
ORA-01830: date format picture ends before converting entire input string / Select sum where date query
提问by user3036627
I am trying to execute my query through Java like this:
我正在尝试通过 Java 执行我的查询，如下所示：
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);
}
Then i call execSumStatement in the class:
然后我在类中调用 execSumStatement ：
sql.execSumStatement2 () ;
When I run it I get the following error message:
当我运行它时，我收到以下错误消息：
java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string
java.sql.SQLDataException: ORA-01830: 日期格式图片在转换整个输入字符串之前结束
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)
When I execute the same query in SQL Developer it works just fine. I already made a search over the internet, and I guess the problem is the datatype of the date in the query, which should be Date, but everything I tried didn't work...
当我在 SQL Developer 中执行相同的查询时，它工作得很好。我已经在互联网上进行了搜索，我猜问题是查询中日期的数据类型，应该是日期，但是我尝试的所有方法都不起作用......
I'm a beginner in programming so it would be great if you could explain it as simple as possible! Thanks in advance!
我是编程的初学者，所以如果你能尽可能简单地解释它会很棒！提前致谢！
回答by Rahul Tripathi
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')
or like
或喜欢
datenum >= date '2013-09-01'
回答by heythere
You can try this:
你可以试试这个：
Select To_date ('15/2/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS'),
To_date ('28/2/2007 10:12', 'DD/MM/YYYY HH24:MI:SS')
From DUAL;
Source:http://notsyncing.org/2008/02/manipulando-fechas-con-horas-en-plsql-y-sql/
资料来源：http : //notsyncing.org/2008/02/manipulando-fechas-con-horas-en-plsql-y-sql/
回答by Abbas Tofighi
What you have written in your sql string is a Timestampnot Date. You must convert it to Dateor change type of database field to Timestampfor it to be seen correctly.
您在 sql 字符串中所写的是Timestampnot Date。您必须将其转换为Date或更改数据库字段的类型Timestamp才能正确查看它。
回答by nidhil ch
In SQL Developer ..Go to Preferences-->NLS-->and change your date format accordingly
在 SQL Developer ..转到首选项--> NLS--> 并相应地更改您的日期格式
回答by Bablu Ahmed
You can try as follows it works for me
你可以尝试如下它对我有用
select * from nm_admission where trunc(entry_timestamp) = to_date('09-SEP-2018','DD-MM-YY');
OR
或者
select * from nm_admission where trunc(entry_timestamp) = '09-SEP-2018';
You can also try using to_char but remember to_char is too expensive
你也可以尝试使用 to_char 但记住 to_char 太贵了
select * from nm_admission where to_char(entry_timestamp) = to_date('09-SEP-2018','DD-MM-YY');
The TRUNC(17-SEP-2018 08:30:11) will give 17-SEP-2018 00:00:00 as a result, you can compare the only date portion independently and time portion will skip.
因此，TRUNC(17-SEP-2018 08:30:11) 将给出 17-SEP-2018 00:00:00，您可以独立比较唯一的日期部分，而时间部分将跳过。
回答by Jitender Kumar
You can use
您可以使用
Select to_date('08/15/2017 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') from dual;
Select to_date('08/15/2017 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') from dual;
If you are using it in an SP then your variable datatype should be Varchar2
如果您在 SP 中使用它，那么您的变量数据类型应该是 Varchar2
and also in your ado.net code the datatype of your input parameter should be
并且在您的 ado.net 代码中，您的输入参数的数据类型应该是
OracleDbType.Varchar2
OracleDbType.Varchar2
Basically I had to put a DateFrom and DateTo filter in my SP so I passed dates as String in it.
基本上我必须在我的 SP 中放置一个 DateFrom 和 DateTo 过滤器，所以我将日期作为字符串传递给它。
Note:This is one of the solution which worked for me, there could be more solutions to this problem.
注意：这是对我有用的解决方案之一，此问题可能有更多解决方案。
