Need help with a query thats driving me insane

[Oliver Ng]

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

[Jeanne Boyarsky]

You may find it easier to use a prepared statement. That way JDBC takes care of creating the proper date format.
Different databases have different formatting requirements for dates.

[Avi Abrami]

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.

The URL for the relevant Web page is:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions137a.htm#1003595
Assuming you haven't set the NLS_DATE_FORMAT parameter (since it is optional), but only the NLS_TERRITORY parameter (since it is mandatory), this Web page shows some of the default date formats for several values of NLS_TERRITORY:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch3.htm#49984
Alternatively, Oracle's (thin) JDBC driver supports the JDBC "escape syntax". More details are available here:
http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
Example:

String query = "SELECT count(*) as NUM_ORDERS " + "FROM web_order_header " + "WHERE CREATE_DATE >= " + "{d '2003-01-01'}" + " AND CREATE_DATE <= " + "{d '2003-02-28'}" + "'";

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:

SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
Date d1 = sdf.parse(date1);
Timestamp ts = new Timestamp(d1.getTime());

See the javadoc for more details:
http://java.sun.com/j2se/1.4.1/docs/api/java/text/SimpleDateFormat.html
(Using the "PreparedStatement" is left as an exercise ;-)
Hope this has helped you.
Good Luck,
Avi.

[Daniel Dunleavy]

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

[Oliver Ng]

thanks for all your replies.. i will try these out and see if it leads to a solution