ORA-01722: invalid number

I did without having the to_number() but still it is giving me the issue. I am trying to do the following inside select statement: select
(case

when #endDate# - least(#startDate# + (nDays * #granularity#), #endDate#) < #granularity#

then #endDate# - (#granularity# - 1)

else least(#startDate# + ((nDays) * #granularity#), #endDate#)

end )
 when I use to_number() in the difference it says:Got interval of time expected NUMBER
when I dont use to_number() in the diff of two dates:I get error: Expected Interval of time got NUMBER.

ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER I would appreciate if some one can help in solving the issue.
--- Cause: java.sql.SQLException: ORA-01722: invalid number


at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)

at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

... 72 more

Caused by: java.sql.SQLException: ORA-01722: invalid number


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1120)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1144)

at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3520)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)

at $Proxy56.execute(Unknown Source)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)

Start Free Trial