File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need help with a query thats driving me insane" Watch "Need help with a query thats driving me insane" New topic
Author

Need help with a query thats driving me insane

Oliver Ng
Greenhorn

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
Marshal

Joined: May 26, 2003
Posts: 31050
    
162

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

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:

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:

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
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
Dan
Oliver Ng
Greenhorn

Joined: Aug 28, 2001
Posts: 15
thanks for all your replies.. i will try these out and see if it leads to a solution
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help with a query thats driving me insane