I have a query that says: String query = "SELECT count(*) as NUM_ORDERS " +"FROM web_order_header " +"WHERE CREATE_DATE >= '" + date1.trim() + "' AND CREATE_DATE <= '" + date2.trim() + "'"; where dates are in the format 01-jan-2003 when i try to run the statment through a stmt.executeQuery(query), i get an error saying java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected can someone please tell me what is wrong with this SQL statement? It seems to work fine using SQLNavigator... i'm really stuck
Hi Oliver, I presume that the variables "date1" and "date2" are instances of "java.lang.String". Oracle will try to convert a string literal (in an SQL statement) representing a date into the (database) DATE datatype -- based on the default date format. Here is an excerpt from the Oracle9i SQL Reference, Release 2 (9.2) (Part Number A96540-02):
The default date format is determined implicitly by the NLS_TERRITORY initialization parameter, or can be set explicitly by the NLS_DATE_FORMAT parameter.
Your third alternative is to use "PreparedStatement" -- as Jeanne has suggested -- however, I believe she is implying that you need to convert "date1" (and "date2") from a "String" to a "java.sql.Timestamp" -- using the "java.text.DateFormat" class. Example:
When you pass the select statement you can include the date strings in the oracle to_date function so that oracle can process them as dates database_dt > to_date( 'date string', 'yyyymmdd') hope that helps Dan
Joined: Aug 28, 2001
thanks for all your replies.. i will try these out and see if it leads to a solution