Narendra M Narendra M - 20 days ago 8
Java Question

getting java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

i am trying to run the below query i am getting the a non-numeric character was found where a numeric was expected here is the query

select * from schedule where to_date(SHDL_STRT_TIME,'dd-MON-yy HH24:mi:ss')
IN('11-Jan-15 20:30:00','11-Jan-15 08:00:00','11-Jan-15 20:00:00')


and
shdl_strt_time
is of type
DATE
object it works fine in sqldeveloper but from application its not working

Please clarify where I am going wrong, any help would be appreciated. Thank You

Answer

SHDL_STRT_TIME is already of type DATE, so you don't need to use TO_DATE() over it.

But a TO_CHAR() which is what you needed!!!

Also MON gives month as JAN... SO, you need a Mon for Jan

select * from schedule where to_char(SHDL_STRT_TIME,'dd-Mon-yy HH24:mi:ss')                                            
IN('11-Jan-15 20:30:00','11-Jan-15 08:00:00','11-Jan-15 20:00:00')

Finally, elegant solution could be,

select * from schedule where SHDL_STRT_TIME IN(:DATE1, :DATE2, :DATE3)

In JDBC, bind the values using java.sql.Date. Because, Mon may not be Jan is Non-American databases!!! Also, you might miss the index, if you perform to_char() over it..