40

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 >= '01-09-2013 00:00:00' and Datum <= '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...

starball
  • 20,030
  • 7
  • 43
  • 238
user3036627
  • 403
  • 1
  • 4
  • 4
  • You can put: Select SUM(A) as NCCSeptember from NCC where Datum between to_date('01-09-2013 00:00:00') and to_date('30-09-2013 23:59:59') – Roberto Góes Mar 21 '19 at 14:21

7 Answers7

48

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'
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 2
    Format for date literal (second option) is incorrect. It should be `yyyy-mm-dd`(`date '2013-09-01'`). Moreover, when comparing dates take into consideration the time portion of a date. – Nick Krasnov Nov 26 '13 at 14:48
  • 1
    I just tried the first solution and it worked ! If i change the settings of the datestamp to "DD/MM/RRRR HH24:MI:SS" in SQL Developer to add the time and change the order of Month and Days, should "to_date('26/11/2013 14:34:45','dd/mm/yyyy hh24:mm:ss')" work ? PS: Thanks a lot for the quick answer ! – user3036627 Nov 26 '13 at 15:12
  • 1
    I got this error because I simply forgot one 'y' in the 'YYYY' pattern for the year – guillaume guerin Mar 22 '17 at 14:58
13

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/

Regular Jo
  • 5,190
  • 3
  • 25
  • 47
heythere
  • 141
  • 1
  • 2
2

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

nidhil ch
  • 21
  • 2
1

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.

Stuart Siegler
  • 1,686
  • 4
  • 30
  • 38
Abbas Tofighi
  • 81
  • 1
  • 2
1

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.

Bablu Ahmed
  • 4,412
  • 5
  • 49
  • 64
0

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.

Jitender Kumar
  • 2,439
  • 4
  • 29
  • 43
0
SELECT CAST(COLUMN_NAME AS DATE) FROM TABLE_NAME
cigien
  • 57,834
  • 11
  • 73
  • 112
Leno Tavares Cabral
  • 19
  • 2