ORA-01830 date format picture ends before converting entire input string Select sum where date query

Question

I am trying to execute my query through Java like this   public ResultSet execSumStatment2   throws SQLException  String query    Select SUM A  as NCCSeptember from NCC where Datum  gt    01-09-2013 00 00 00  and Datum  lt    30-09-2013 23 59 59    return execStatement query       Then i call execSumStatement in the class   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   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      I m a beginner in programming so it would be great if you could explain it as simple as possible  Thanks in advance

User · Answer

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  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

User · Answer

In SQL Developer   Go to Preferences-- NLS-- and change your date format accordingly

User · Answer

You can use   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  and also in your ado net code the datatype of your input parameter should be  OracleDbType Varchar2  Basically I had to put a DateFrom and DateTo filter in my SP so I passed dates as String in it   Note  This is one of the solution which worked for me  there could be more solutions to this problem

User · Answer

What you have written in your sql string is a Timestamp not Date  You must convert it to Date or change type of database field to Timestamp for it to be seen correctly

User · Answer

I think you should not rely on the implicit conversion  It is a bad practice   Instead you should try like this   datenum  gt   to date  11 26 2013   mm dd yyyy     or like  datenum  gt   date  2013-09-01

User · Answer

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

[oracle] ORA-01830: date format picture ends before converting entire input string / Select sum where date query

Examples related to oracle

Examples related to date

Examples related to select

Examples related to ora-01830