aspose 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
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

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: 1132

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