File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Need help with a query thats driving me insane Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Need help with a query thats driving me insane" Watch "Need help with a query thats driving me insane" New topic

Need help with a query thats driving me insane

Oliver Ng

Joined: Aug 28, 2001
Posts: 15
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
author & internet detective

Joined: May 26, 2003
Posts: 33124

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.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

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

The URL for the relevant Web page is:
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:
Alternatively, Oracle's (thin) JDBC driver supports the JDBC "escape syntax". More details are available here:

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.

See the javadoc for more details:
(Using the "PreparedStatement" is left as an exercise ;-)
Hope this has helped you.
Good Luck,
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
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
Oliver Ng

Joined: Aug 28, 2001
Posts: 15
thanks for all your replies.. i will try these out and see if it leads to a solution
It is sorta covered in the JavaRanch Style Guide.
subject: Need help with a query thats driving me insane
It's not a secret anymore!