SQL date format bind variable

Question

Can anyone please tell me what is wrong with this SQL query.

"select USERNAME, DATECREATED, CREDTYPE, USAGETYPE, OPERATIONID, TXNID, CALLERID from arwfissuanceauditlog where OPERATIONID in (2104,2107) and datecreated >= 'to_date('2010/06/21', 'yyyy/MM/dd HH24:MI:SS')' and datecreated < 'to_date('2010/08/03', 'yyyy/MM/dd HH24:MI:SS')'"

I got this message when i ran it in SQL developer Unknown Command Bind Variable "MI" is NOT DECLARED

i am calling this from a java code like this try { stmt = conn.createStatement(); results = stmt.executeQuery(queryToExecute); } catch (SQLException e) { CredChangeReportSVC.DEFAULTLOGGER.error("An exception occurred while executing query: " + queryToExecute, e); }

In the logs i get this message ERROR 03 Aug 2010 14:51:06,939 - An exception occurred while executing query: "select USERNAME, DATECREATED, CREDTYPE, USAGETYPE, OPERATIONID, TXNID, CALLERID from arwfissuanceauditlog where OPERATIONID in (2104,2107) and datecreated >= 'to_date('2010/06/21', 'yyyy/MM/dd HH24:MI:SS')' and datecreated < 'to_date('2010/08/03', 'yyyy/MM/dd HH24:MI:SS')'" java.sql.SQLException: Invalid SQL type

Thanks in advance.

Answer 1

Your date format specifies a time part, but you have omitted this in your datetime strings. Drop the "HH24:MI:SS" from the date strings and try again